C#: Left outer joins with LINQ

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)

Select TL.EntryDate, TL.Hours, J.JobName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo

A LINQ query using inner join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo
    where tl.ResourceNo == resourceNo

    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

And a LINQ query performing left join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo into tl_j
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

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 from the above LINQ expression.

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job]
FROM [dbo].[TimeSheetLine] AS [t0]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
WHERE [t0].[ResourceNo] = @p0

Similar concept can be expanded for multiple left joins. Assuming that a TimeSheetLine will either have a JobNo or an IndirectCode, consider this SQL query:

Select TL.EntryDate, TL.Hours, J.JobName, I.IndirectName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo
Left Join Indirects I on TL.IndirectCode=I.IndirectCode

The equivalent LINQ query is:

var lines =
    from tl in db.TimeSheetLines
    join j in db.Jobs      on tl.JobNo        equals j.JobNo            into tl_j
    join i in db.Indirects on tl.IndirectCode equals i.IndirectCode  into tl_i
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    from i in tl_i.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName,
        Indirect = i.IndirectName,
    };

And the generated SQL is:

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job], [t2].[IndirectName] As [Indirect]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
LEFT OUTER JOIN [dbo].[Indirects] AS [t2] ON [t0].[IndirectCode] = [t2].[IndirectCode]
WHERE [t0].[ResourceNo] = @p0

That’s all, left outer joins in LINQ are as easy as in T-SQL. Happy joining.

---

Possibly related posts: (automatically generated)

• No Title
• OBIEE Modeling Best Practices