英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论