linq 将聚合与分组链接

oxalkeyp 于 2023-01-06 发布在其他

关注(0)|答案(3)|浏览(145)

我正在翻译一个查询，下面是原文：

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

以下是我目前掌握的情况：

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

我不知道如何包含count()和group by子句。当我包含groupBy时，其他列会消失。谢谢。

linq

来源：https://stackoverflow.com/questions/75027291/linq-an-aggregate-with-a-grouping

3条答案

按热度按时间

laximzn51#

好吧，这不是最有效的，但它的工作：

var topUsers = _context.Sessions
                .Where(s => s.SDate > DateTime.Now.AddYears(-1))
                .GroupBy(s => s.UsrId)
                .Select(ws => new { ws.Key, cnt = ws.Count() })
                .OrderByDescending(s => s.cnt)
                .Take(5);

var topNamedUsers = topUsers.Join(_context.Users, ws => ws.Key, ud => ud.UsrId, (ws, ud) => new { ws.Key, ud.FirstName, ud.LastName, ws.cnt });

赞(0）回复(0）举报 2023-01-06

cngwdvgl2#

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 g
           select new
           {
               UsrId = g.Key,
               FirstName = g.Select(x => x.FirstName).FirstOrDefault(),
               LastName = g.Select(x => x.LastName).FirstOrDefault(),
               sCount = g.Count()
           })
           .OrderByDescending(d => d.sCount)
           .Take(5);

赞(0）回复(0）举报 2023-01-06

khbbv19g3#

这是对你的答案的回答--不是对你最初的查询的回答。我想把它作为注解，但是不格式化它很难解释假设User对象有Session的集合，你的第一个语句可以大大简化：

var topUsers = _context.Sessions
        .Where(s => s.SDate > DateTime.Now.AddYears(-1))
        .Select(s => new
        {
            s.UsrId,
            cnt = s.User.Sessions.Count(u => u.UsrId == s.UsrId)
        })
        .OrderByDescending(s => s.cnt)
        .Take(5);

您可以对结果进行整形，以获得一个同时具有FirstName和LastName的ViewModel。

赞(0）回复(0）举报 2023-01-06

我来回答

linq 将聚合与分组链接

3条答案

相关问题

热门标签

最新问答