C# Linq 按跨不同表的多个字段分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11524171/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq group by multiple fields across different tables
提问by Stokedout
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
在尝试将此 SQL 查询转换为 LINQ 时，我遇到了 group by 问题。如何按来自多个表的多个列进行分组？下面的 linq 查询根本无法编译，所以我需要正确的语法
Original SQL Query
原始 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 Query attempt (not working)
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};
采纳答案by Jeff Mercado
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.
首先，您的查询并不等效。次要的事情真的很喜欢检查不同的值，但主要的事情是您不按相同的键分组。您在查询中遗漏的按 JobCode 分组的 SQL。你试图分组太多而不是订购它。您聚合的值应该是您要分组的值。
As for why you're getting the syntax error, after you do a continuation (using intoin a group byor selectclause), all the variables in the previous scope are lost and only the continuation variable remains (g1in your case). You tried to reference ewhich is now out of scope giving you the problem.
至于为什么会出现语法错误，在执行延续（into在group byorselect子句中使用）之后，前一个作用域中的所有变量都将丢失，仅保留延续变量（g1在您的情况下）。您试图引用e现在超出范围的引用，给您带来了问题。
The query should be more like this I think:
我认为查询应该更像这样：
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),
};
