LINQ Coding

LINQ coding
-------------------------------
//from c in MurSapeliCovers
//join o in MurCompCustomers on new { cid = c.CustomerId, coid=c.CoverId} equals new
//{ cid = o.CustId, coid=o.CoverId} into g
//from o in g.DefaultIfEmpty()
//select new {Cover = c.Cover,
// compNumber = o == null ? 0 : o.CompNumber
// }


from p in MurCompApplications
join o in MurCompCustomers on p.CompNumber equals o.CompNumber
join c in MurSapeliCovers on new { cid = o.CustId, coid=o.CoverId} equals new
{ c.CustomerId = cid, CoverId=coid} into g
from c in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}

from c in MurSapeliCovers
join o in ( from cu in MurCompCustomers join ca in MurCompApplications
on cu.CompNumber equals ca.CompNumber
select new { CustId = cu.CustId, CoverId = cu.CoverId, CompNumber=ca.CompNumber } )
on new { cid = c.CustomerId, coid=c.CoverId} equals new
{ cid = o.CustId, coid=o.CoverId} into g
from o in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}


from p in MurCompApplications
join q in MurCompCustomers on p.CompNumber equals q.CompNumber
select new
{
coverid = q.CoverId, compNumber = p.CompNumber
}
///////////////////////////////////
from p in MurCompApplications
join q in MurCompCustomers on p.CompNumber equals q.CompNumber into g
from q in g.DefaultIfEmpty()
select new
{
coverid = q == null ? 0 : q.CoverId,
compNumber = p.CompNumber
}
///////////////////////////////////
from c in MurSapeliCovers
join o in MurCompCustomers on new { cid = c.CustomerId, coid=c.CoverId} equals new
{ cid = o.CustId, coid=o.CoverId} into g
from o in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}
///////////////////////////////////

from c in MurSapeliCovers
join o in ( from cu in MurCompCustomers join ca in MurCompApplications
on cu.CompNumber equals ca.CompNumber
select new { CustId = cu.CustId, CoverId = cu.CoverId, CompNumber=ca.CompNumber } )
on new { cid = c.CustomerId, coid=c.CoverId} equals new
{ cid = o.CustId, coid=o.CoverId} into g
from o in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}
from sapelicovers in TblCoverWithCustomerDetails
join compData in
(
from compCust in Comp_Customers
join compApp in Comp_Applications
on compCust.CompNumber equals compApp.Comp_CompNumber.CompNumber
where compApp.Active == true
&& compApp.Comp_TypeDetails.TypeId == 1
&& compApp.Comp_TypeDetails.Code == 1
select new
{
CompNumber = compCust.CompNumber,
CustId = compCust.CustId,
CoverId = compCust.CoverId,
IncidentDate = compApp.IncidentDate,
Active = compApp.Active,
CompTypeId = compApp.Comp_TypeDetails.TypeId,
CompTypeCode = compApp.Comp_TypeDetails.Code
}
)
on
new { coverno = sapelicovers.CoverNo , custid = sapelicovers.CustomerNo }
equals new { coverno = compData.CoverId , custid = compData.CustId }
into outer
from compData in outer.DefaultIfEmpty()
where sapelicovers.CustomerNo == 1
select new
{
Cover = sapelicovers.Cover,
InsuranceNumber = sapelicovers.InsuranceNo,
CompensationNumber = compData == null ? 0 : compData.CompNumber,
StatusCode = sapelicovers.CoverStatus,
EvaluationDate = compData.IncidentDate,
ExpiryReason = sapelicovers.ExpiryReason,
ValidFromDate = sapelicovers.CoverStartDate,
ValidToDate = sapelicovers.CoverEndDate,
SumInsured = sapelicovers.SumInsured,
Bonus = sapelicovers.BonusType,
Benificiary = sapelicovers.BeneficiaryCode
}

from sapelicover in TblCoverWithCustomerDetails
join o in
(
from p in Comp_Customers
join q in Comp_Applications
on p.CompNumber equals q.Comp_CompNumber.CompNumber
where q.Comp_TypeDetails.TypeId == 1
&& q.Comp_TypeDetails.Code == 1
&& p.CompNumber == 10005
&& q.Active == false
select new
{
coverid = p.CoverId,
CompNumber = p.CompNumber,
AddInfoReceivedDate = q.AddInfoReceivedDate,
AppReceivedDate = q.AppReceivedDate,
AddiInfo = q.Comments,
IncidentDate = q.IncidentDate,
CustId = p.CustId,
InsuranceNumber = p.InsuranceId
}
)
on new { coverid = sapelicover.CoverNo, custid = sapelicover.CustomerNo }
equals new { coverid = o.coverid, custid = o.CustId } into outer
from o in outer.DefaultIfEmpty()
where
sapelicover.Active == true
&& sapelicover.CustomerNo == 1
&& sapelicover.CoverNo == 1
select new
{
sapelicover, o
}














C# Expression
-------------
from p in Comp_Applications select p
from p in Comp_Applications select p.CompNumber
from p in Comp_Applications select new{p.RowId,p.CompNumber}
from p in Comp_Applications select new{Comp_Number=p.CompNumber}
from p in Comp_Applications select new{ rowid= p.RowId, comp_number= p.CompNumber}
from p in Comp_Applications where p.RowId > 10 select p
from p in Comp_Applications where p.CompNumber==10011 select p
from p in Comp_Applications where p.RowId > 10 && p.CompNumber==10011 select p
from p in Comp_Applications where p.RowId > 10 || p.CompNumber==10011 select p
from p in Comp_Applications orderby p.CreatedBy,p.CompNumber select p
from p in Comp_Applications group p by p.CreatedBy into g select new{ crea=g.Key}
from p in Comp_Applications group p by p.CreatedBy into g select new{ crea=g.Key, count=g.Count()}


var one = from p in Comp_Applications select p;
var two = from p in Comp_Applications select new{ rowid = p.RowId , compNumber = p.CompNumber };
one.Dump();
two.Dump();

from p in Comp_Applications
select new { p.CompNumber, CompeNumber = p.CompNumber > 10005 && p.CompNumber < 10008 ? 1 :
p.CompNumber > 10009 && p.CompNumber < 10020 ? 0:
p.CompNumber,
recDate = p.AppReceivedDate
}


outer join example:-
============================

create table MurCustomer
(
keyId int, customerName varchar(50)
)
create table MurOrder
(
keyId int, OrderNumber varchar(50)
)
insert into MurCustomer values(1,'xx');
insert into MurCustomer values(2,'yy');
insert into MurCustomer values(3,'zz');
insert into MurCustomer values(4,'aa');
insert into MurCustomer values(5,'bb');

insert into MurOrder values(1,'pppp');
insert into MurOrder values(1,'qqqq');
insert into MurOrder values(4,'rrrr');
insert into MurOrder values(5,'ssss');
insert into MurOrder values(5,'tttt');

select * from MurOrder;
select * from MurCustomer;


from c in MurCustomers
join o in MurOrders on c.KeyId equals o.KeyId into g
from o in g.DefaultIfEmpty()
select new {customerNameDisplay = c.CustomerName,
OrderNumber = o == null ? "(no orders)" : o.OrderNumber}


another example:-
===================
create table MurSapeliCover
(
CustomerId int, CoverId int, Cover varchar(50)
)
create table MurCompApplication
(
CompNumber int
)
create table MurCompCustomer
(
CompNumber int, CustId int, CoverId int
)

insert into MurSapeliCover values(1,1,'First cust 1st Cover');
insert into MurSapeliCover values(1,2,'First cust 2nd Cover');
insert into MurSapeliCover values(1,3,'First cust 3rd Cover');
insert into MurSapeliCover values(1,4,'First cust 4th Cover');

insert into MurSapeliCover values(2,1,'Second cust 1st Cover');
insert into MurSapeliCover values(2,5,'Second cust 2nd Cover');
insert into MurSapeliCover values(3,6,'Third cust 1st Cover');
insert into MurSapeliCover values(4,7,'Fourth cust 1st Cover');

insert into MurCompApplication values(1);
insert into MurCompApplication values(2);
insert into MurCompApplication values(3);

insert into MurCompCustomer values(1,1,1);
insert into MurCompCustomer values(2,1,2);
insert into MurCompCustomer values(3,2,1);


select * from MurSapeliCover;
select * from MurCompCustomer;
select * from MurCompApplication;

from c in MurSapeliCovers
join o in MurCompCustomers on new { cid = c.CustomerId, coid=c.CoverId} equals new
{ cid = o.CustId, coid=o.CoverId} into g
from o in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}


from c in MurSapeliCovers
join o in ( from cu in MurCompCustomers join ca in MurCompApplications
on cu.CompNumber equals ca.CompNumber
select new { CustId = cu.CustId, CoverId = cu.CoverId, CompNumber=ca.CompNumber } )
on new { cid = c.CustomerId, coid=c.CoverId} equals new
{ cid = o.CustId, coid=o.CoverId} into g
from o in g.DefaultIfEmpty()
select new {Cover = c.Cover,
compNumber = o == null ? 0 : o.CompNumber
}

from p in Comp_Customers where p.CustId == 100
select new { p, p.CompNumber, p.CustId }


ClaimsCRUD.GetCustomerSapeliCovers(request);
*/