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

Solution 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.

Solution 2

This is multiple left join sample with null checking (preventing null reference exception). Thnx to Nick!

void Main()
{
    var data = DataAccess.GetData();
    var res = 
        from m in data.Movies
        join ma in data.MovieActor on m.Id equals ma.MovieId into mma
        from ma in mma.DefaultIfEmpty()
        join p in data.People on (ma == null ? 0 : ma.ActorId) equals p.Id into pma
        from p in pma.DefaultIfEmpty()
        orderby m.Name
        select new {
            Movie = m.Name,
            Actor = p != null ? p.Name + " as " + ma.Name : ""
        };
    foreach (var el in res)
    {
        Console.WriteLine($"{el.Movie} - {el.Actor}");
    }
}

public class DataAccess
{
    public static Data GetData()
    {
        var list = new Data
        {
            Movies = new List<Movie>{
             new Movie{ Id = 1, Name= "Raiders of the Lost Ark", Year = 1981},
             new Movie{ Id = 2, Name= "Blade Runner", Year = 1982},
             new Movie{ Id = 3, Name= "Star Wars: Episode IV - A New Hope", Year = 1977},
             new Movie{ Id = 4, Name= "Total Recall", Year = 1990},
             new Movie{ Id = 5, Name= "The Fugitive", Year = 1993},
             new Movie{ Id = 6, Name= "Men in Black", Year = 1997},
             new Movie{ Id = 7, Name= "U.S. Marshals", Year = 1998},
             new Movie{ Id = 8, Name= "Batman", Year = 1989},
             new Movie{ Id = 9, Name= "A Few Good Men", Year = 1992},
             new Movie{ Id = 10, Name= "Tropic Thunder", Year = 2008},
             new Movie{ Id = 11, Name= "Minority Report", Year = 2002},
             new Movie{ Id = 12, Name= "The Fifth Element", Year = 1997},
             new Movie{ Id = 13, Name= "District 9", Year = 2009},
             new Movie{ Id = 14, Name= "12 Monkeys", Year = 1995},
            },
            People = new List<Person>{
                new Person{ Id = 1, Name = "Harrison Ford"},
                new Person{ Id = 2, Name = "Tommy Lee Jones"},
                new Person{ Id = 3, Name = "Will Smith"},
                new Person{ Id = 4, Name = "Michael Keaton"},
                new Person{ Id = 5, Name = "Will Smith"},
                new Person{ Id = 6, Name = "Jack Nicholson"},
                new Person{ Id = 7, Name = "Tom Cruise"}
            },
            MovieActor = new List<MovieActor>{
                new MovieActor{ MovieId = 1, ActorId = 1, Name = "Indy"},
                new MovieActor{ MovieId = 2, ActorId = 1, Name = "Rick Deckard"},
                new MovieActor{ MovieId = 3, ActorId = 1, Name = "Han Solo"},
                new MovieActor{ MovieId = 5, ActorId = 1, Name = "Dr. Richard Kimble"},
                new MovieActor{ MovieId = 5, ActorId = 2, Name = "Samuel Gerard"},
                new MovieActor{ MovieId = 6, ActorId = 2, Name = "Kay"},
                new MovieActor{ MovieId = 7, ActorId = 2, Name = "Samuel Gerard"},
                new MovieActor{ MovieId = 6, ActorId = 3, Name = "Jay"},
                new MovieActor{ MovieId = 8, ActorId = 4, Name = "Batman / Bruce Wayne"},
                new MovieActor{ MovieId = 8, ActorId = 6, Name = "Joker / Jack Napier"},
                new MovieActor{ MovieId = 9, ActorId = 6, Name = "Col. Nathan R. Jessep"},
                new MovieActor{ MovieId = 9, ActorId = 7, Name = "Lt. Daniel Kaffee"},
                new MovieActor{ MovieId = 10, ActorId = 7, Name = "Les Grossman"},
                new MovieActor{ MovieId = 11, ActorId = 7, Name = "Chief John Anderton"}
            }

        };
        return list;
    }
}

public class Data
{
    public List<Movie> Movies = new List<Movie>();
    public List<Person> People = new List<Person>();
    public List<MovieActor> MovieActor = new List<MovieActor>();
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Movie
{
    public int Id { get; set; }
    public string Name {get; set;}
    public int Year { get; set; }
}

public class MovieActor
{
    public int MovieId { get; set; }
    public int ActorId { get; set; }
    public string Name { get; set; } // appearance as
}