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

score 125 · Accepted Answer · edited Nov 23 '15 at 18:29

125

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.

edited Nov 23 '15 at 18:29

Peter Mortensen

30,738
21
105
131

answered Jun 17 '13 at 07:29

Sergey Berezovskiy

232,247
41
429
459

how to handle the date thing? @lazyberezovsky – Ehsan Sajjad Jun 17 '13 at 07:49
@EhsanSajjad welcome :) I just was testing query and found one issue - when you do left outer join, then some of values `p, m` can be null. It's OK for simple select, but when you are doing grouping, you can see something like `null cannot be assigned to Int32`. So here is workaround - when selecting anonymous object do `prno = p == null ? 0 : p.prno`. That will do the trick – Sergey Berezovskiy Jun 17 '13 at 07:51
@EhsanSajjad to handle date thing use [EntityFunctions.TruncateTime Method](http://msdn.microsoft.com/en-us/library/system.data.objects.entityfunctions.truncatetime.aspx) – Sergey Berezovskiy Jun 17 '13 at 07:54
2

i did'nt got your drouping point, why you are doing into x after select, is it necessary? @lazyberezovsky – Ehsan Sajjad Jun 17 '13 at 08:50
@EhsanSajjad yes, it is necessary, otherwise you can't apply `group by` method – Sergey Berezovskiy Jun 17 '13 at 08:51
so where i need to add the code you told in the second last comment..@lazyberezovsky – Ehsan Sajjad Jun 17 '13 at 09:05
@EhsanSajjad when selecting anonymous type, just before grouping: `select new { d.bookingid, prno = p == null ? 0 : p.prno, ... } into x` – Sergey Berezovskiy Jun 17 '13 at 09:06
10

if any of the previous join results in null, then I'm getting an null reference exception. how can I solve that issue? – Badhon Jain Sep 10 '14 at 05:46
5

I am having the same problem. If the first outer join results in a null, then the 2nd outer join throws a null reference exception. – jkruer01 Apr 06 '15 at 04:47
3

I have the same issue as above^. How to check for a null condition of the first left join object? – ProxyTech May 06 '15 at 18:40
5

As with an outer join in SQL, you need to check for null values before attempting to use any fields. For example, `m` above can be null so in the `where` clause the predicate `m.branchId == 6` should either have a conditional such as `(m == null ? 0 : m.branchId)` or, using the new safe navigation operator in C# 6, `m?.branchId ?? 0`. The same applies when assigning values to the properties of the anonymous result object. – Nick Mar 15 '16 at 16:35
@SergeyBerezovskiy can u plz check the same @ [here](https://stackoverflow.com/questions/49095385/converting-an-sql-query-with-both-inner-join-and-left-join-to-linq) – Zaker Mar 04 '18 at 12:29
I have a problem with this solution, in the second from I can't compare column from first table(d) and third table only columns of second table (bookingm) are available in (m) – Martin Varga Feb 13 '19 at 07:16
How to write this in Linq-To-Sql representation? – Alexander Sep 09 '21 at 14:56

score 11 · Answer 2 · answered Oct 14 '19 at 09:00

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
}

LINQ to SQL multiple tables left outer join

2 Answers2

Linked