Next & Previous

LINQ left join

When you write LINQ query syntax (from x in stuff) you can have as many from clauses as you like. Using this technique and the .DefaultIfEmpty() extension, we can write a sensible left outer join:

from quote in _context.Quotes
from buildingsCover in _context.BuildingsCover
	.Where(bc => bc.QuoteId == quote.Id)
from contentsCover in _context.ContentsCover
	.Where(cc => cc.QuoteId == quote.Id)
select new { quote, buildingsCover, contentsCover };

In this example, we have a quote which represents buildings insurance and/or contents insurance.

In the returned list of objects, one or both of buildingsCover and contentsCover can be null, but quote will always be set.

Using .DefaultIfEmpty() gives you a collection where every item is null, instead of a collection of 0 items. This has an important effect on the outcome of the join.

This idea is not altogether different from something I wrote two years ago about implementing a full outer join in SQL by chaining multiple selections with a comma - it’s the old style of SQL joins. LINQ allows a similar mechanic, and we’re seeing that in action here.

Tuesday again… no problem!