C# and .NET Tips and Tricks

Left Outer Join in LINQ to Entities (for Entity Framework 4)

In this post we will explore the most practical ways of performing left outer joins in LINQ to Entities. The same principles applying to LINQ to Entities for EF 4 also apply to LINQ2SQL. This was not the case before EF 4 mainly due to the absence of the DefaultIfEmpty() method in the Entity Framework. In an case, in this post we focus on EF4.

For our test we will use the following demo database/conceptual model:

 

 

As you can see, Master records may have 1 or more Detail records and Detail records may have one or more DetailOfDetail records. Left outer joins are translated as follows:

 

“Bring me all records/entities from the Master table along with their corresponding Detail records. The Master records should always exist in the result set even if there are no related records/entities in the Detail table”.

 

The first approach is using the navigation property Details in the query as follows:

 

from p in ctx.Masters.Include("Details") select p

Query1() method in the project

 

This approach is the simplest possible. It only applies when you have the “Details” Navigation Property on your model (enabling the property just to be able to perform left outer join queries is not an option) and it also suffers from the fact that the data are not flattened  (which you may need since your data are feeding a report for example). You get a list of Master objects each one with a list of 0 or more Detail objects.

But you have a way of flattening your results. Using the following LINQ will flatten your results but unfortunately will perform an inner join:

 

var Result2 = from p in ctx.Masters
              from q in p.Details
              select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                  d1=q.DetailField1,d2=q.DetailField2};

Query2() method in the project

 

To do a left outer join you just need to do the following:

 

var Result2 = from p in ctx.Masters
              from q in p.Details.DefaultIfEmpty()
              select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                  d1=q.DetailField1,d2=q.DetailField2};

Query3() method in the project

 

That is, the only difference is the addition of the .DefaultIfEmpty() method (this one was missing in previous versions of EF). Now you have a flattened result set for your needs.

 

The second approach does not require the navigation property:

If you do not have the “Details” navigation property then you can still get a left outer join. Using the following will give you an inner join:

 

var Result3 = from p in ctx.Masters join q in ctx.Details on p.Id equals q.MasterID
              select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                  d1 = q.DetailField1,d2 = q.DetailField2};

Query4() method in the project

 

Changing this to the followinq:

 

var Result3 = from p in ctx.Masters 
              join q in ctx.Details on p.Id equals q.MasterID into Details
              select new {Id = p.Id,d=Details};

 

Will give you the same result as if you had the navigation property. All you have to do for the left outer join is to flatten it as before:

 

var Result3 = from p in ctx.Masters 
              join q in ctx.Details on p.Id equals q.MasterID into Details
              from m in Details.DefaultIfEmpty()
              select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                  d1 = m.DetailField1,d2 = m.DetailField2};

Query5() method in the project

 

Now let’s extend it a little further and require to get results from the third table as well. We will distinguish again between the two previous approaches (obviously we would like to avoid a nasty foreach loop at the end where the data are collected with SELECT statements creating a wonderful SELECT N+1 issue).

 

Using the Navigation Property Approach

To include the data from the third table in your result you just follow the same principle by adding the third table:

 

var Result2 = from p in ctx.Masters
              from q in p.Details.DefaultIfEmpty()
              from l in q.Details.DefaultIfEmpty()
              select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                  d1=q.DetailField1,d2=q.DetailField2,dd1=l.DetailOfDetailField1,dd2=l.DeailOfDetailField2};

Query6() method in the project

 

Without the Navigation Property

The same applies here by following the same approach as before:

 

var Result3 = from p in ctx.Masters 
                              join m in ctx.Details on p.Id equals m.MasterID into Details
                              from q in Details.DefaultIfEmpty() join o in ctx.DetailOfDetails 

                                   on q.Id equals o.DetailID into DetailsOfDetail
                              from l in DetailsOfDetail.DefaultIfEmpty()
                              select new
                              {
                                  Id = p.Id,
                                  m1 = p.MasterField1,
                                  m2 = p.MasterField2,
                                  d1 = q.DetailField1,
                                  d2 = q.DetailField2,
                                  dd1 = l.DetailOfDetailField1,
                                  dd2 = l.DeailOfDetailField2
                              };

Query7() method in the project

 

 

(Note that since in the project we are not using the database the DefaultIsEmpty method gets a parameter defining the default object.)