SQL转LINQ：如何实现带分组的Count聚合并关联查询？

需要把一段查询近一年会话数排名前5的用户的SQL转成LINQ，要包含用户ID、姓名及会话统计数。当前写的LINQ在添加GroupBy后丢失了其他字段，不知道怎么同时实现Count聚合、GroupBy并保留所需字段。

原始SQL #

select top 5 t.usrID, u.FirstName, u.LastName, t.cnt as sCount
from (
    select usrID, COUNT(rID) as cnt
    from sessions as s where s.sDate > DATEADD(yy, -1, getdate())
    group by usrID
) as t
    inner join users as u on t.usrID = u.usrID
order by t.cnt desc

当前LINQ代码 #

var topUser = (from p in _context.Sessions
               where p.SDate > DateTime.Now.AddYears(-1)
               join c in _context.Users on p.UsrId equals c.UsrId into j1
               from j2 in j1.DefaultIfEmpty()
              // group j2 by p.UsrId into grouped
               select new
               {
                   p.UsrId,
                   j2.FirstName,
                   j2.LastName,
                   cnt = p.RId
               })
                //.OrderBy(d => d.cnt)
                //.GroupBy(o => o.UsrId)
               .Take(5);

原始SQL的核心逻辑是先统计每个用户近一年的会话数，再关联用户表获取姓名，最后按会话数倒序取前5，LINQ可以完全贴合这个逻辑来写，避免先关联再分组导致的字段丢失问题：

方法1：贴近原始SQL的查询语法写法 #

var topUsers = (
    from s in _context.Sessions
    where s.SDate > DateTime.Now.AddYears(-1)
    // 先按用户ID分组，统计会话数（对应SQL子查询）
    group s by s.UsrId into sessionGroup
    // 关联用户表获取姓名信息
    join u in _context.Users on sessionGroup.Key equals u.UsrId
    // 按会话数倒序排序
    orderby sessionGroup.Count(s => s.RId != null) descending
    // 构造最终返回的结果对象
    select new 
    { 
        u.UsrId, 
        u.FirstName, 
        u.LastName, 
        sCount = sessionGroup.Count(s => s.RId != null) 
    }
).Take(5);

方法2：简洁的方法链写法 #

var topUsers = _context.Sessions
    .Where(s => s.SDate > DateTime.Now.AddYears(-1))
    .GroupBy(s => s.UsrId)
    // 先聚合每个用户的会话数
    .Select(g => new 
    { 
        UsrId = g.Key, 
        SessionCount = g.Count(s => s.RId != null) 
    })
    // 关联用户表
    .Join(_context.Users, 
          stat => stat.UsrId, 
          u => u.UsrId, 
          (stat, u) => new 
          { 
              u.UsrId, 
              u.FirstName, 
              u.LastName, 
              sCount = stat.SessionCount 
          })
    .OrderByDescending(result => result.sCount)
    .Take(5);

关键说明 #

• 先对Sessions按UsrId分组聚合，这一步和SQL的子查询完全对应，避免了先关联再分组的字段丢失问题
• Count(s => s.RId != null)对应SQL里的COUNT(rID)，如果RId不会为空，直接用g.Count()即可
• 最后按会话数倒序排序后取前5，和原始SQL逻辑一致

内容的提问来源于stack exchange，提问作者Kyle