Outer join in LINQ to SQL
LINQ to SQL provides direct access to inner joins only. However, it does take much to write an outer join either.
from l in context.LeftTable join x in context.RightTable on l.Id equals x.LeftTableId into y from r in y.DefaultIfEmpty()
Unfortunately I can no longer remember where I learned this from so I can’t give credit.
Joel Albahari points out in an MSDN thread that it is not necessary to use the
join keyword at all and presents this, (IMO) much more understandable version:
from l in context.LeftTable from r in context.RightTable.Where(x => x.LeftTableId == l.Id).DefaultIfEmpty()
The above snippets accomplish the same as
from LeftTable l left join RightTable r on l.Id = r.LeftTableId
in regular SQL. The key in both solutions is the DefaultIfEmpty()