我在SQL中有此查询,并且希望它使用Entity Framework在LINQ中实现它,但是如何应用左外部联接的多个表?
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语法):
join...into
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
该查询联接了三个表。您可以以相同的方式加入其余表。