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

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.

< br > via < a class="StackLink" href=" http://stackoverflow.com/questions/832/" >How do I most elegantly express left join with aggregate SQL as LINQ query< /a>

Share on Google Plus

About Cinema Guy

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.