如何在single linqtosql语句中使用subquery、groupby、max和top?

很简单。子查询将我们投影到ID。查询将获取具有匹配ID的记录。

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

进一步考虑,您可以避开子查询:

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

作为您提供的查询,我可以解释为这个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;

我的SQL fu不是很好,在我第一杯咖啡之前,所以我假设外部查询中的“l”最终与子查询中的“l”完全不同?

我 认为 这样就可以了,但您必须确保:)检查生成的SQL是什么样的,这是非常值得的。当然,如果您不介意它作为两个查询执行,它会更简单一些。

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

(编辑:我以前没用过max moddate。多哈。还通过使用ID作为键(它已经是键)简化了分组,因此我们只需要moddate作为组值。)