Multiple Outer Joins In LINQ to SQL

If you already know how to form outer joins in LINQ, you’d probably assume that cascading outer joins (join X to Y, then join Y to Z) could be constructed easily by just repeating the syntax.  As with many things LINQ, not so fast, my friends…

Trying to get something like this:

select ...
from ORDER O
left outer join PRODUCT P on P.ProductId = O.ProductId
left outer join MATERIAL M on M.MaterialId = P.MaterialId
where ...

The hunch would be that this would work:

from o in dc.Orders

join p in dc.Products on o.ProductId equals p.ProductId into prods
from p in prods.DefaultIfEmpty()

join m in Materials on p.MaterialId equals m.MaterialId into mats
from m in mats.DefaultIfEmpty()

But, if you hit a case where the first outer join clause’s result is null, the 2nd clause will throw an exception.  Debug, and step through it, to see it happen.  (Curiously, it actually works in LINQPad — and even generates the correct SQL.)

Give Google a run, and you’ll likely see everyone suggesting that the above should work for multiple outer joins.  But, if you look closely, you’ll also notice that, in all of those examples, the poster’s demo will have each of the outer joins constructed against the table in the from clause (from X, join Y to X, join Z to X) and not — the 1st join against the table in the from clause — and the 2nd join against the table in the 1st join (from X, join Y to X, join Z to Y).

Alas, there is never just one way to construct a query in LINQ.  And, the same holds true for outer joins:

from o in dc.Orders

//outer join usual way...
join p in dc.Products on o.ProductId equals p.ProductId into prods
from p in prods.DefaultIfEmpty()

//can also be written with this format...
from m in Materials.Where(mats => mats.MaterialId == p.MaterialId).DefaultIfEmpty()

That results in the exact same outer join SQL syntax as the typical method.  (Additionally, this alternate format also allows you to write joins on multiple columns in what feels like a more traditional, natural SQL way.)   From there, you can circumvent the null exception with a short-circuit null check:

from o in dc.Orders

//also written in alternate format...
from p in Products.Where(prods => prods.ProductId == o.ProductId).DefaultIfEmpty()

//2nd outer join taking advantage of a short-circuiting check for null...
from m in Materials.Where(mats => p != null && (mats.MaterialId == p.MaterialId)).DefaultIfEmpty()

Which works!  It results in the SQL below.  Notice the addition of the “test is not null” clause in the 2nd join.  That’s our null check in the code, and those are the bits that allow us to avoid an exception upon evaluation when that 1st table has null results.  I’m still not certain what the traditional format is doing under the covers that causes it to except (even when LINQPad shows that it does generate “correct” SQL).  But, the bottom line is we do have an alternate format, which isn’t ackward to write, which works:

...
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[MaterialId], [t1].[ProductId], [t1]...
    FROM [dbo].[PRODUCT] AS [t1]
    ) AS [t2] ON [t0].[ProductId] = ([t2].[ProductId])

LEFT OUTER JOIN (
    SELECT 1 AS [test], [t3].[ProductId], [t3]...
    FROM [dbo].[MATERIAL] AS [t3]
    ) AS [t4] ON ([t2].[test] IS NOT NULL) AND ([t4].[MaterialId] = [t2].[MaterialId])
...

Enjoy.

Page 1 of 212
Rss Feed