Linq按不同表中的多个字段分组 [英] Linq group by multiple fields across different tables

问题描述

试图将此SQL查询转换为LINQ，我遇到了group by问题. 如何对源自多个表的多个列进行分组? 下面的linq查询根本无法编译，因此我需要正确的语法

In an attempt to convert this SQL query to LINQ I have hit a problem with group by. How can I group by multiple columns that originate from multiple tables? The linq query below simply won't compile so I'm in need of the correct syntax

select LTRIM(RTRIM(e.Shortname)) 
Name, LTRIM(RTRIM(j.JobName)) 
JobName, j.JobCode, 
SUM(Hours) Hours, 
MAX(ce.LatestTimesheetEntry) LastTimeSubmitted 
from TWCTL.TW.Postings p
join TWCTL.TW.Employees e on e.EmployeeId = p.EmployeeId
join TWCTL.TW.Jobs j on j.JobCode = p.JobCode
join CentralTimeEmployees.dbo.Employees ce on ce.CtEmployeeId = e.EmployeeId
where (e.CostCentreId = 1 or e.CostCentreId = 3) 
and (p.TransactionDate >= '2012-01-01' and p.TransactionDate <= '2012-07-01') 
and j.JobCode <> 'CTCIT00001' 
and ce.DatabaseCode = 'CTL' 
and (ce.CostCentreId = 1 or ce.CostCentreId = 3)
group by j.JobName, j.JobCode, Shortname
order by e.Shortname, j.JobName

Linq查询尝试(不起作用)

var model = 
        from p in context.Postings
        join e in context.Employees on p.EmployeeId equals e.EmployeeId
        join j in context.Jobs on p.JobCode equals j.JobCode
        join ce in context.CentralTimeEmployees on e.EmployeeId equals ce.CtEmployeeId
        where (e.CostCentreId == 5 || e.CostCentreId == 3)
                && (p.TransactionDate >= fromDate
                    && p.TransactionDate <= toDate)
                && j.JobCode != "CTCIT00001"
                && ce.DatabaseCode == "CTL"
                && (ce.CostCentreId == 1 || ce.CostCentreId == 3)
        group j by new {j.JobName, j.JobCode} into g1
        group e by e.Shortname into g2    <- doesnt work??        
        select
            new ProjectHoursViewModel
                {Posting = p, Job = g1.Key.JobName, Employee = e, CentralTimeEmployee = ce};

推荐答案

对于初学者而言，您的查询不相同.次要事情确实喜欢检查不同的值，但是主要的事情是您不按相同的键分组.在查询中遗漏的按JobCode分组的SQL.您试图将其分组过多，而不是对其进行订购.您正在汇总的值应该是您要分组的值.

For starters, your query is not equivalent. Minor things really like different values being checked but a major one is that you don't group by the same keys. The SQL grouped by JobCode which you left out in your query. And you attempted to group too much instead of ordering it. The values you are aggregating should be what you are grouping.

关于为什么出现语法错误的原因，在执行连续操作(在group by或select子句中使用into)之后，先前作用域中的所有变量都将丢失，而只有连续变量保留(在您的情况下为g1).您试图引用e，但现在超出了范围，从而给您带来了问题.

As for why you're getting the syntax error, after you do a continuation (using into in a group by or select clause), all the variables in the previous scope are lost and only the continuation variable remains (g1 in your case). You tried to reference e which is now out of scope giving you the problem.

查询应该更像这样:

var fromDate = new DateTime(2012, 1, 1);
var toDate = new DateTime(2012, 7, 1);
var query = 
    from p in dc.Postings
    join e in dc.Employees on p.EmployeeId equals e.EmployeeId
    join j in dc.Jobs on p.JobCode equals j.JobCode
    join ce in dc.CentralTimeEmployees on e.EmployeeId equals ce.CtEmployeeId
    where (e.CostCentreId == 1 || e.CostCentreId == 3) // the SQL tested 1 or 3
       && (p.TransactionDate >= fromDate && p.TransactionDate <= toDate)
       && j.JobCode != "CTCIT00001"
       && ce.DatabaseCode == "CTL"
       && (ce.CostCentreId == 1 || ce.CostCentreId == 3)
    group new { ce.Hours, ce.LatestTimesheetEntry }
       by new { j.JobName, j.JobCode, e.ShortName } into g
    orderby g.Key.ShortName, g.Key.JobName
    select new
    {
        Name = g.Key.ShortName.Trim(),
        JobName = g.Key.JobName.Trim(),
        JobCode = g.Key.JobCode,
        Hours = g.Sum(x => x.Hours),
        LastTimeSubmitted = g.Max(x => x.LatestTimesheetEntry),
    };

这篇关于Linq按不同表中的多个字段分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！