Linq- Select Column with count-CodePudding

I need a simple Linq query to get the employee records along with the count from foreign key table (Dept). Following query is not working

System.InvalidOperationException: 'The LINQ expression 'GroupByShaperExpression: KeySelector: q.DeptId , ElementSelector:new ................ .Select(x => x.EmpName) .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

var query = from h in context.Employee
                        join p in context.Dept on h.EmpId equals p.DeptId
                        select new
                        {
                            h.EmpId,
                            h.EmpName,
                            h.Salary,
                            p.DeptId
                        };

var groupQuery = from q in query
                         group q by q.DeptId into g
                         select new 
                         {
                             DeptCount = g.Count(),
                             Empname=g.Select(s=>s.EmpName).First(),
                             Salary = g.Select(s => s.Salary).First(),
                             EmpId = g.Select(s => s.EmpId).First()
                         };

return groupQuery.ToList();

Table scehma:

CodePudding user response：

As sql, you should group for every not aggregated field that you want to see

Try this:

var query = from h in context.Employee
                    join p in context.Dept on h.EmpId equals p.DeptId
                    select new
                    {
                        h.EmpId,
                        h.EmpName,
                        h.Salary,
                        p.DeptId
                    };

var groupQuery = from q in query
                         group q by new {EmpId= q.EmpId, EmpName = q.EmpName, Salary = q.Salary} into g
                         select new 
                         {
                             DeptCount = g.Count(),
                             Empname=g.Key.EmpName,
                             Salary = g.Key.Salary,
                             EmpId = g.Key.EmpId
                         };

return groupQuery.ToList();

You can also do the same in this way

var query = from h in context.Employee
            join p in context.Dept on h.EmpId equals p.DeptId
            group new {h, p} by h into g
                     select new 
                     {
                         DeptCount = g.Count(),
                         Empname=g.Key.EmpName,
                         Salary = g.Key.Salary,
                         EmpId = g.Key.EmpId
                     };

You sould also pay attention in case of Employee with no Dept and use DefaultIfEmpty on join:

var query = from h in context.Employee
        join p in context.Dept on h.EmpId equals p.DeptId into xp
        from jp in xp.DefaultIfEmpty()
        group new {h, jp} by h into g
                 select new 
                 {
                     DeptCount = g.Count(),
                     Empname = g.Key.EmpName,
                     Salary = g.Key.Salary,
                     EmpId = g.Key.EmpId
                 };