使用LINQ进行分组的聚合。

huangapple go评论76阅读模式
英文:

Linq an aggregate with a grouping

问题

这是您的查询的翻译:

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
               group p by p.UsrId into grouped
               let count = grouped.Count()
               orderby count descending
               select new
               {
                   UsrID = grouped.Key,
                   FirstName = grouped.FirstOrDefault().User.FirstName,
                   LastName = grouped.FirstOrDefault().User.LastName,
                   sCount = count
               })
               .Take(5);

希望这可以帮助您解决问题。

英文:

I'm translating a query and here's the original:

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

Here's what I have so far:

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

I'm having trouble figuring out how to include count() and group by clauses. When I include groupBy my other columns disappear. Thank you.

答案1

得分: 1

这是对你的回答的回答 - 不是对你最初的查询的回答。我会将其放在注释中,但是没有格式很难解释。

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

你可以塑造结果以获得一个ViewModel,其中还包括FirstName和LastName。这一切都归结为定义具有一对多关系的模型。

英文:

This is the answer to your answer - not to your original query. I would put it as comment, but without formatting it's hard to explain
Assuming User object has collection of Session your first statement can be drastically simplified:

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

You can shape the results to get a ViewModel that also has FirstName and LastName. It all boils down to defining a model with one-to-many relationship

答案2

得分: 0

以下是翻译好的代码部分:

好的,虽然它不是最高效的,但它可以工作:

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 });
英文:

Alright so it's not the most efficient but it works:

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

答案3

得分: 0

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

英文:
           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);

huangapple
  • 本文由 发表于 2023年1月6日 13:35:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75027291.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定