英文:
Linq an aggregate with a grouping
问题
这是您的查询的翻译：
var topUser = (from p in _context.Sessionswhere p.SDate > DateTime.Now.AddYears(-1)join c in _context.Users on p.UsrId equals c.UsrIdgroup p by p.UsrId into groupedlet count = grouped.Count()orderby count descendingselect 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 sCountfrom (select usrID, COUNT(rID) as cntfrom sessions as s where s.sDate > DATEADD(yy, -1, getdate())group by usrID) as tinner join users as u on t.usrID = u.usrIDorder by t.cnt desc
Here's what I have so far:
var topUser = (from p in _context.Sessionswhere p.SDate > DateTime.Now.AddYears(-1)join c in _context.Users on p.UsrId equals c.UsrId into j1from j2 in j1.DefaultIfEmpty()// group j2 by p.UsrId into groupedselect 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.Sessionswhere p.SDate > DateTime.Now.AddYears(-1)join c in _context.Users on p.UsrId equals c.UsrId into j1from j2 in j1.DefaultIfEmpty()group j2 by p.UsrId into gselect 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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论