Linq-選擇帶有計數的列-有解無憂

我需要一個簡單的 Linq 查詢來獲取員工記錄以及外鍵表（部門）的計數。以下查詢不起作用

System.InvalidOperationException: 'LINQ 運算式 'GroupByShaperExpression: KeySelector: q.DeptId , ElementSelector:new ....Select(x => x.EmpName) .First() ' 無法翻譯。以可翻譯的形式重寫查詢，或通過插入對“AsEnumerable”、“AsAsyncEnumerable”、“ToList”或“ToListAsync”的呼叫顯式切換到客戶端評估。有關詳細資訊，請參閱https://go.microsoft.com/fwlink/?linkid=2101038 。

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

表模式： Linq-選擇帶有計數的列

uj5u.com熱心網友回復：

作為 sql，您應該為每個要查看的未聚合欄位分組

嘗試這個：

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

你也可以這樣做

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

您還應該注意沒有 Dept 的 Employee 并在加入時使用 DefaultIfEmpty：

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454030.html

標籤：C＃实体框架林克

上一篇：在LINQ中的陣列中搜索特定字串

下一篇：Linq如何根據嵌套串列屬性和外部串列的重疊過濾物件串列