Linq an aggregate with a grouping-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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