如何在我的复杂LINQ查询中获取组总和

Question

下面我列出了一个LINQ查询，该查询在我的ASP.NET.MVC Web应用程序中正常工作。
此外，我想对"津贴。

            var query = from ara in aras
                    join company in companies on ara.Id equals company.ARAId
                    join wasteWater in wasteWaters on company.Id equals wasteWater.CompanyId
                    join allowance in allowances on wasteWater.Id equals allowance.WasteWaterID
                    join parameter in parameters on allowance.ParameterId equals parameter.Id into JoinedParameterAllowance
                    from parameter in JoinedParameterAllowance.DefaultIfEmpty()
                    where company.Activ == true && company.End == null && company.Template == false
                    && wasteWater.End == null
                    select new FreightSummaryViewModel
                     {
                         AraName = ara.Name,
                         AraId = ara.Id,
                         AllowedParameter = parameter.Name,
                         AllowedFreight = allowance.Freight
                    };

我试图插入"组..."，但未能正确。有人可以帮我设置正确的语法吗？预先感谢您，Manu

Answer 1

我对您的数据库中的关系一无所知，所以我即兴...

// Some dummy data to play with
var aras        = Enumerable.Range(0,  5).Select(i => new { Id = i, Name = "Ara" + i });
var companies   = Enumerable.Range(0, 15).Select(i => new { Id = i, ARAId = i % 5, Activ = true, End = (DateTime?)null, Template = false });
var wasteWaters = Enumerable.Range(0, 35).Select(i => new { Id = i, CompanyId = i / 15, End = (DateTime?)null });
var allowances  = Enumerable.Range(0, 70).Select(i => new { Id = i, WasteWaterID = i, ParameterId = i % 4, Freight = i * 1000 });
var parameters  = Enumerable.Range(0,  4).Select(i => new { Id = i, Name = "Parameter" + i });

这就是我相信您要寻找的：

var query =
    from ara in aras
        join company    in companies   on ara.Id                equals company.ARAId
        join wasteWater in wasteWaters on company.Id            equals wasteWater.CompanyId
        join allowance  in allowances  on wasteWater.Id         equals allowance.WasteWaterID
        join parameter  in parameters  on allowance.ParameterId equals parameter.Id
            into JoinedParameterAllowance
//  from parameter in JoinedParameterAllowance.DefaultIfEmpty()
    where true
        && company.Activ    == true
        && company.End      == null
        && company.Template == false
        && wasteWater.End   == null
    group allowance by new
    {
        AraName     = ara.Name,
        AraId       = ara.Id,
        ParameterId = allowance.ParameterId
    } into myGroup
    select new //FreightSummaryViewModel
    {
        AraName          = myGroup.Key.AraName,
        AraId            = myGroup.Key.AraId,
        AllowedParameter = myGroup.Key.ParameterId,
        AllowedFreight   = myGroup.Sum(g => g.Freight)
    };