使用EF实现左外连接

本文关键字：连接 实现 EF 使用 | 更新日期: 2023-09-27 18:03:13

我正在尝试在VS2015中使用c#将sql语句转换为EF

下面是SQL语句: 
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 )

我试图实现连接，但我被困在如何左外连接使用into和DefaultIfEmpty()在PerformaInv表然后on i.cdin_cdindexid=p.pinv_CDIndexId

这就是我想要的:

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,
             });

我知道有很多东西缺失，但我真的很糟糕。

使用EF实现左外连接

正如我在评论中提到的，最好使用导航属性。但是一旦你开始使用手动连接，将内连接转向左外连接(尽管不是自然的)就不是那么难了。

给定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
...

通过使用连接子句(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 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()
...
相关文章：