c# - Linq group by 跨不同表的多个字段

Question

在尝试将此 SQL 查询转换为 LINQ 时，我遇到了分组依据问题。如何按源自多个表的多个列进行分组？下面的 linq 查询根本无法编译，所以我需要正确的语法

原始 SQL 查询

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

Answer 1

对于初学者来说，您的查询是不等价的。次要的事情真的很喜欢检查不同的值，但主要的是你不按相同的键分组。按您在查询中遗漏的 JobCode 分组的 SQL。你试图分组太多而不是排序。您聚合的值应该是您分组的值。

至于为什么你会得到语法错误，在你做了一个延续之后(在 group by 或 select 子句中使用 into )，先前作用域中的所有变量都将丢失，只保留延续变量(在您的情况下为 g1)。您尝试引用现在超出范围的 e 给您带来问题。

我认为查询应该更像这样:

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