LINQ to SQL multiple tables left outer join

Question

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 

Answer 1

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.