Performing Multiple Joins in EF

Hello everyone

i want to perform joins in EF from SQL statement but, i tried to but i stopped because i feel that there's somthing wrong.

this is SQL statement:

Select i.cdin_cdindexid,p.pinv_PerformaInvID,coalesce(i.cdin_serial,0) as depno
,coalesce(convert(datetime,left(convert(nvarchar,i.cdin_startunstufdate,120),10),120),'-') as deidate,coalesce(i.cdin_goodsDesc,'-') as gooddesc ,coalesce(i.cdin_Customdeclar,'-') as custdec
,coalesce(i.cdin_NoofPackages,0) as pkg,coalesce(i.cdin_WT,0) as wt ,coalesce(i.cdin_volumewt,0) as vwt ,coalesce(i.cdin_MortgageAmount,0) as lcamt,coalesce(p.pinv_name,'-') as invno,coalesce(p.pinv_TotalAmount,0) as invamt,p.pinv_Status,p.pinv_InvoiceProperty as prop
,coalesce(c.comp_name,'-') as custname,coalesce(Comp_CompanyId,'-') as custid ,coalesce(c.comp_idcust,'-') as accpacno,coalesce(t.Terr_Caption,'-') as Terr,convert(nvarchar,'01',2) as type
from cdindex i inner join company c on i.cdin_CompanyId =c.Comp_CompanyId inner join Territories t on i.cdin_Secterr =t.Terr_TerritoryID left outer join PerformaInv p on i.cdin_cdindexid=p.pinv_CDIndexId
where(cdin_deleted Is null And c.comp_deleted Is null And t.Terr_Deleted Is null And p.pinv_deleted Is null)
and cdin_startunstufdate between '2016-06-01' and '2016-07-28'
and (p.pinv_status in('Draft','Posted') or pinv_status is null) and (p.pinv_InvoiceProperty ='01' or p.pinv_InvoiceProperty is null )

This is the code i tried to type in EF 6.0/C#

(from i in db.CDIndexes
join c in db.Companies on i.cdin_CompanyId equals c.Comp_CompanyId
join t in db.Territories on i.cdin_Secterr equals t.Terr_TerritoryID
join p in db.PerformaInvs.DefaultIfEmpty() on i.cdin_CDIndexID equals p.pinv_CDIndexId
where (i.cdin_startunstufdate>= new DateTime(2016 - 06 - 01) && i.cdin_startunstufdate>= new DateTime(2016-06-28) &&
p.pinv_Status is 'Draft' || p.pinv_Status=='Posted')

i have a big issue with implementing leftouter join and the where cluase conditions.

can anyone help me with this code

Or if the sql select statement is complex like that is there any way to code an application with EF.

thank you all

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Fares Ayyad10y ago
0
Bikesh Srivastava
Sorry but in you code does not have left outer join
because into and .DefaultIfEmpty() method isn't appeare in you code ?
Bikesh Srivastava10y ago
Hi,
You can use like this.
varentryPoint=(fromepindbContext.tbl_EntryPoint
joineindbContext.tbl_Entryonep.EIDequalse.EID
jointindbContext.tbl_Titleone.TIDequalst.TID
wheree.OwnerID==user.UID
selectnew{
UID=e.OwnerID,
TID=e.TID,
Title=t.Title,
EID=e.EID
}).Take(10);