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


Translate

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

Alle Antworten
  • Translate

    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.


  • Hyman Lee
    Translate
    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()
        };
    

    Just youse the above code and this should work fine!


  • Translate

    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.


  • Translate

    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);
        }
    }