How do I most elegantly express left join with aggregate SQL as LINQ query

Question

SQL:

SELECT    u.id,    u.name,    isnull(MAX(h.dateCol), '1900-01-01') dateColWithDefault FROM universe u LEFT JOIN history h     ON u.id=h.id     AND h.dateCol<GETDATE()-1 GROUP BY u.Id, u.name 

Answer 1

A solution, albeit one that defers handling of the null value to the code, could be:

DateTime yesterday = DateTime.Now.Date.AddDays(-1);

var collection=     from u in db.Universe     select new     {         u.id,         u.name,         MaxDate =(DateTime?)        (            from h in db.History            where u.Id == h.Id            && h.dateCol < yesterday            select h.dateCol         ).Max()     }; 

This does not produce exactly the same SQL, but does provide the same logical result. Translating "complex" SQL queries to LINQ is not always straightforward.