LINQ到SQL多个表的左外连接 [英] LINQ to SQL multiple tables left outer join

问题描述

我在SQL此查询，我希望它使用实体框架来实现它在LINQ，但如何申请多个表左外连接？

  SELECT d.bookingid，
       d.labid，
       d.processid，
       p.prid，
       p.prno，
       d.DestinationBranchID，
       d.SendStatus
从dc_tpatient_bookingdÐ
       LEFT OUTER JOIN dc_tpatient_bookingm M于d.bookingid = m.bookingid
       LEFT OUTER JOIN dc_tpatient P于p.prid = m.prid
       LEFT OUTER JOIN dc_tp_test T ON d.testid = t.testid
       LEFT OUTER JOIN dc_tp_groupm通用汽车t.groupid = gm.groupid
       LEFT OUTER JOIN dc_tpanel PN ON m.panelid = pn.panelid
       LEFT OUTER JOIN dc_tp_organization OG ON og.orgid = m.clientid
       LEFT OUTER JOIN dc_tp_ward W于w.wardid = m.wardid
       LEFT OUTER JOIN dc_tp_branch TB ON tb.BranchID = m.BranchID
WHERE d.processid = 6
       AND（（m.branchId = 1
               与d.DestinationBranchID = 0）
              OR（d.DestinationBranchID = 1
                   与d.sendstatus =R））
       和d.testid I​​N（SELECT testid
                        从dc_tp_test
                        WHERE subdepartmentid = 13）
       和DATE_FORMAT（m.enteredon，'％Y /％M /％D'）之间达成2013年6月15日和2013年6月15日
GROUP BY m.bookingid
ORDER BY d.priority DESC，
       m.bookingid ASC
 

解决方案

下面是外左侧如何联接与LINQ实现。您应该使用群组加入（加入到... 语法）：

 从D在context.dc_tpatient_bookingd
加入bookingm在context.dc_tpatient_bookingm
     在d.bookingid等于bookingm.bookingid到bookingmGroup
从米bookingmGroup.DefaultIfEmpty（）
在dc_tpatient加入病人
     在m.prid等于patient.prid到patientGroup
从patientGroup.DefaultIfEmpty P（）
// ...其他加入这里
其中，d.processid == 6安培;＆安培;
      （（m.branchId == 1安培;＆安培; d.DestinationBranchID == 0）||
       （d.DestinationBranchID == 1和;＆放大器; d.sendstatus ==R））
这里// ...其他条件
排序依据d.priority下降，m.bookingid
选择新{
   d.bookingid，
   d.labid，
   d.processid，
   p.prid，
   p.prno，
   m.bookingid //需要分组
}成X个
团X由x.bookingid为G
选择G以
 

此查询连接三个表。你可以加入表的其余部分以同样的方式。

I have this query in SQL, and I want it to implement it in LINQ using Entity Framework, but how can I apply multiple tables left outer joins?

SELECT d.bookingid,
       d.labid,
       d.processid,
       p.prid,
       p.prno,
       d.DestinationBranchID,
       d.SendStatus
FROM   dc_tpatient_bookingd d
       LEFT OUTER JOIN dc_tpatient_bookingm m ON d.bookingid = m.bookingid
       LEFT OUTER JOIN dc_tpatient p ON p.prid = m.prid
       LEFT OUTER JOIN dc_tp_test t ON d.testid = t.testid
       LEFT OUTER JOIN dc_tp_groupm gm ON t.groupid = gm.groupid
       LEFT OUTER JOIN dc_tpanel pn ON m.panelid = pn.panelid
       LEFT OUTER JOIN dc_tp_organization og ON og.orgid = m.clientid
       LEFT OUTER JOIN dc_tp_ward w ON w.wardid = m.wardid
       LEFT OUTER JOIN dc_tp_branch tb ON tb.BranchID = m.BranchID
WHERE  d.processid = 6
       AND ( ( m.branchId = 1
               AND d.DestinationBranchID = 0 )
              OR ( d.DestinationBranchID = 1
                   AND d.sendstatus = 'R' ) )
       AND d.testid IN (SELECT testid
                        FROM   dc_tp_test
                        WHERE  subdepartmentid = 13)
       AND date_format(m.enteredon, '%Y/%m/%d') BETWEEN '2013/06/15' AND '2013/06/15'
GROUP  BY m.bookingid
ORDER  BY d.priority DESC,
       m.bookingid ASC

解决方案

Here is how left outer joins are implemented with LINQ. You should use GroupJoin (join...into syntax):

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm
     on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
     on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()
// ... other joins here
where d.processid == 6 &&
      ((m.branchId == 1 && d.DestinationBranchID == 0) ||
       (d.DestinationBranchID == 1 && d.sendstatus == "R"))
// ... other conditions here
orderby d.priority descending, m.bookingid
select new {
   d.bookingid,
   d.labid,
   d.processid,
   p.prid,
   p.prno,
   m.bookingid // need for grouping
} into x
group x by x.bookingid into g
select g

This query joins three tables. You can join the rest of the tables the same way.

这篇关于LINQ到SQL多个表的左外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！