Performing Multiple Joins in EF

Question

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