score:3

Accepted answer 
from l in db.Leads
from a in l.LeadAddresses.Select(la => la.Address).OrderByDescending(a => a.ID).Take(1).DefaultIfEmpty()
select new { l, a }

This might look tricky, but you understand it part by part:

  1. Using OrderByDescending in combination with Take(1) we take the address with the maximum ID
  2. Using DefaultIfEmpty we create a left-join.

Be aware that this pattern forces a loop-join due to limitation of SQL Server. For small result sets this is usually not a problem.


Related Articles