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
Advertisement
Answer
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.