C#: Left outer joins with LINQ
June 10, 2009 — Syed Mehroz Alam
I always considered Left Outer Join in LINQ to be complex until today when I had to use it in my application. I googled and the first
result gave a very nice explanation. The only difference between ordinary joins (inner joins) and left joins in LINQ is the use of “join into” and “DefaultIfEmpty()” expressions.
Consider this very simple query (Assuming a scenario that not all the TimesheetLines are associated with a Job)
1 | Select TL.EntryDate, TL.Hours, J.JobName |
3 | Left Join Jobs J on TL.JobNo=J.JobNo |
A LINQ query using inner join is
02 | from tl in db.TimeSheetLines |
03 | join j in db.Jobs on tl.JobNo equals j.JobNo |
04 | where tl.ResourceNo == resourceNo |
08 | EntryDate = tl.EntryDate, |
And a LINQ query performing left join is
02 | from tl in db.TimeSheetLines |
03 | join j in db.Jobs on tl.JobNo equals j.JobNo into tl_j |
04 | where tl.ResourceNo == resourceNo |
06 | from j in tl_j.DefaultIfEmpty() |
09 | EntryDate = tl.EntryDate, |
Notice that the only difference is the use of “into” with the join statement followed by reselecting the result using “DefaultIfEmpty()” expression. And here’s the generated SQL for the above LINQ expression.
1 | SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job] |
2 | FROM [dbo].[TimeSheetLine] AS [t0] |
3 | LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo] |
4 | WHERE [t0].[ResourceNo] = @p0 |
Another LINQ version which is more compact is:
2 | from tl in db.TimeSheetLines |
3 | from j in db.Jobs.Where(j=>j.JobNo == tl.JobNo).DefaultIfEmpty() |
6 | EntryDate = tl.EntryDate, |
Similarly, this concept can be expanded for multiple left joins. Assuming that a TimeSheetLine will either have a JobNo or an IndirectCode, consider this SQL query:
1 | Select TL.EntryDate, TL.Hours, J.JobName, I.IndirectName |
3 | Left Join Jobs J on TL.JobNo=J.JobNo |
4 | Left Join Indirects I on TL.IndirectCode=I.IndirectCode |
The equivalent LINQ query is:
02 | from tl in db.TimeSheetLines |
03 | join j in db.Jobs on tl.JobNo equals j.JobNo into tl_j |
04 | join i in db.Indirects on tl.IndirectCode equals i.IndirectCode into tl_i |
05 | where tl.ResourceNo == resourceNo |
07 | from j in tl_j.DefaultIfEmpty() |
08 | from i in tl_i.DefaultIfEmpty() |
11 | EntryDate = tl.EntryDate, |
14 | Indirect = i.IndirectName, |
And the generated SQL is:
1 | SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job], [t2].[IndirectName] As [Indirect] |
2 | LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo] |
3 | LEFT OUTER JOIN [dbo].[Indirects] AS [t2] ON [t0].[IndirectCode] = [t2].[IndirectCode] |
4 | WHERE [t0].[ResourceNo] = @p0 |
That’s all, left outer joins in LINQ are as easy as in T-SQL. Happy joining.
Update:
Notice that this post describes the approach to perform a Left Outer Join in LINQ To SQL as well as Entity Framework (version 4). The same is not true for Entity Framework version 3.5 since it does not support the DefaultIfEmpty keyword. To perform Left Outer Joins with Entity Framework 3.5, we need to create appropriate relationships (e.g 0..1 to 0..Many) in our Entity Model and they will be automatically translated into TSQL’s Left Join clause.
