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 allways straightforward.

Answer 2

Does this actually produce a left join in the SQL? I don't know anything about linq but it looks like it should produce a subselect? How does one find this kind of thing out?

Answer 3

This isn't a full answer for you, but on the left join piece you can use the DefaultIfEmpty operator like so:

var collection = 
from u in db.Universe
join history in db.History on u.id = history.id into temp
from h in temp.DefaultIfEmpty()
where h.dateCol < DateTime.Now.Date.AddDays(-1)
select u.id, u.name, h.dateCol ?? '1900-01-01'

I haven't had the need to do any groupby commands yet, so I left that out as to not send you down the wrong path. Two other quick things to note. I have been unable to actually join on two parameters although as above there are ways to get around it. Also, the ?? operator works really well in place of the isnull in SQL.

Answer 4

You're going to want to use the join into construct to create a group query.

TestContext db = new TestContext(CreateSparqlTripleStore());
var q = from a in db.Album
        join t in db.Track on a.Name equals t.AlbumName into tracks
        select new Album{Name = a.Name, Tracks = tracks};
foreach(var album in q){
    Console.WriteLine(album.Name);
    foreach (Track track in album.Tracks)
    {
        Console.WriteLine(track.Title);
    }
}