Just how do I most elegantly share left accompany accumulated SQL as LINQ 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
0
2019-05-04 00:22:15
Source Share
Answers: 1

A remedy, albeit one that postpones handling of the void value to the code, can be :

DateTime the other day = 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 generate specifically the very same SQL, yet does give the very same sensible outcome. Converting "intricate" SQL questions to LINQ is not constantly uncomplicated.

0
2019-05-07 17:35:19
Source