c# – Linq group由不同表中的多个字段组成

在尝试将此SQL查询转换为LINQ时,我遇到了group by的问题.
如何按多个来自多个表的列进行分组？
下面的 linq 查询根本不会编译,所以我需要正确的语法

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,Shortname
order by e.Shortname,j.JobName

Linq查询尝试(不工作)@H_502_5@

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.你试图分组太多而不是订购它.您聚合的值应该是您要分组的值.

至于为什么你得到语法错误,在你继续(在group by或select子句中使用into)后,前一个范围中的所有变量都会丢失,只剩下连续变量(在你的情况下为g1).您试图引用现在超出范围的e来解决问题.@H_502_5@

我认为查询应该更像这样：@H_502_5@

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

c# – Linq group由不同表中的多个字段组成

解决方法

猜你在找的C#相关文章