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)
.DefaultIfEmpty()
from contentsCover in _context.ContentsCover
.Where(cc => cc.QuoteId == quote.Id)
.DefaultIfEmpty()
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!