Fares AA Fares AA - 3 months ago 18
SQL Question

Implement left outer join using EF

I'm trying to convert sql statement to EF using c# in VS2015

below 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 )


I tried to implement the joins but i'm stuck in how to left outer join using into and DefaultIfEmpty() on PerformaInv table then
on i.cdin_cdindexid=p.pinv_CDIndexId


This what i tried to:

ar q = (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 into
p from pr in p.DefaultIfEmpty

where (i.cdin_startunstufdate>= new DateTime(2016 - 06 - 01) && i.cdin_startunstufdate>= new DateTime(2016-06-28)

)



select new
{

i.cdin_CDIndexID,
i.cdin_Serial,
i.cdin_startunstufdate,
i.cdin_goodsDesc,
i.cdin_Customdeclar,
i.cdin_NoofPackages,
i.cdin_WT,
i.cdin_volumewt,
i.cdin_MortgageAmount,

});


I know that there is a lot of things missing but i really get sucked.

Answer

As I mentioned in the comments, it's better to use navigation properties. But once you started with manual joins, turning inner join to left outer join (although not natural) is not so hard.

So given your joins

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 on i.cdin_CDIndexID equals p.pinv_CDIndexId
...

you turn any of those to left outer join by using the pattern shown in join clause (C# Reference) - Left Outer Join

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 on i.cdin_CDIndexID equals p.pinv_CDIndexId
// the following line turns the above join to left outer
into p_Join from p in p_Join.DefaultIfEmpty()
...
Comments