How do I use subquery, groupby, max, and top in single linqToSql statement?

Question

Using LinqToSql, I need to return a single (L) for the most recent modDate in a join table (CL).

Tables:
L (Lid, meta1, meta2, ...)
CL (Cid, Lid, ModDate)

Here is sql that produces the expected result

SELECT l.*
FROM L l
INNER JOIN (
 SELECT TOP 1 cl.Lid, MAX(cl.ModDate) as ModDate
 FROM CL cl
 INNER JOIN L l ON cl.Lid = l.Lid AND l.meta1 = 5
 GROUP BY cl.Lid
 ORDER BY MAX(cl.ModDate) DESC
) As m ON l.Lid = m.Lid

Answer 1

My SQL-fu isn't fabulous and it's before my first coffee, so I assume "l" in the outer query ends up being a completely different "l" to the one in the subquery?

I think this will do it, but you'll have to try to be sure :) It'll be well worth checking what the generated SQL looks like. If you didn't mind it executing as two queries, of course, it would be somewhat simpler.

// Can't do the "Take(1)" here or it will be executed separately
var subquery = from cl in context.CL
               join l in context.L on cl.Lid = l.Lid
               where l.meta1 = 5 // could put this in join clause
               group cl.ModDate by cl.lid into grouped
               order by grouped.Max() descending
               select grouped.Key;

// But can take the first result of the join
// This may be simpler using dot notation instead of a query expression
var query = (from l in context.L
            join lid in subquery
            select l).Take(1);

(EDIT: I wasn't taking the max ModDate before. Doh. Also simplified grouping by using the ID as the key (which it was already) so we only need the ModDate as the group values.)

Answer 2

As your provided query, I can interpret into this Linq.

var query = from l in Context.L
            join m in (from cl in Context.CL
                       join l in Context.L on cl.Lid equals l.Lid 
                       where l.meta1 == 5
                       group new { l.Lid, cl.ModDate } by cl.Lid into grp
                       select new { Lid = grp.Key, ModDate = grp.Max(g => g.ModDate) }  into grp
                       order by grp.ModDate descending
                       select grp).Take(1) on l.Lid equals m.Lid
            select l;

Answer 3

Simple enough. The subquery projects us to the ids. The query fetches those records with matching ids.

var subquery = db.L
    .Where(L => L.meta1 = 5)
    .SelectMany(L => L.CLs)
    .GroupBy(CL => CL.Lid)
    .OrderByDescending(g => g.Max(CL => CL.ModDate))
    .Select(g => g.Key)
    .Take(1)

var query = db.L
  .Where(L => subquery.Any(id => L.Lid == id))

Reflecting on this further, you can get away from the subquery:

var query = db.L
  .Where(L => L.meta1 = 5)
  .SelectMany(L => L.CLs)
  .GroupBy(CL => CL.Lid)
  .OrderByDescending(g => g.Max(CL => CL.ModDate))
  .Select(g => g.First().L);