LINQ to SQL 多表左外连接 [英] LINQ to SQL multiple tables left outer join
本文介绍了LINQ to SQL 多表左外连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 SQL 中有这个查询,我希望它使用 实体框架在 LINQ 中实现它,但是如何应用多个表的左外连接?
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
推荐答案
以下是使用 LINQ 实现左外连接的方法.您应该使用 GroupJoin(join...into
语法):
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 to SQL 多表左外连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文