EF7中LINQ跨表聚合Volume并获取城市名称的实现问题

核心需求 #

按城市分组，计算IdBrand为1和19的Bundles记录中Volume的总和，同时关联获取对应城市的名称。

基础实现（仅包含有符合条件Bundles的城市） #

查询语法

using (var context = new YourDbContext())
{
    var cityVolumeSummary = from bundle in context.Bundles
                           join city in context.Cities on bundle.IdCity equals city.IdCity
                           where bundle.IdBrand is 1 or 19 // 也可写成 bundle.IdBrand == 1 || bundle.IdBrand == 19
                           group bundle by new { city.IdCity, city.Name } into cityGroup
                           select new
                           {
                               CityId = cityGroup.Key.IdCity,
                               CityName = cityGroup.Key.Name,
                               TotalVolume = cityGroup.Sum(b => b.Volume)
                           };

    var result = cityVolumeSummary.ToList();
}

方法语法

using (var context = new YourDbContext())
{
    var cityVolumeSummary = context.Bundles
        .Join(context.Cities,
              bundle => bundle.IdCity,
              city => city.IdCity,
              (bundle, city) => new { Bundle = bundle, City = city })
        .Where(joinResult => joinResult.Bundle.IdBrand == 1 || joinResult.Bundle.IdBrand == 19)
        .GroupBy(joinResult => new { joinResult.City.IdCity, joinResult.City.Name })
        .Select(group => new
        {
            CityId = group.Key.IdCity,
            CityName = group.Key.Name,
            TotalVolume = group.Sum(item => item.Bundle.Volume)
        });

    var result = cityVolumeSummary.ToList();
}

关键说明 #

之前城市名称获取错误，大概率是因为分组时仅使用了IdCity作为分组键，后续尝试获取City.Name时，EF无法确保分组内的城市名称一致性（即使逻辑上是一对一关联）。正确的做法是将IdCity和City.Name共同作为分组键，这样每个分组对应的城市信息是明确唯一的。

扩展：包含所有城市（即使无符合条件的Bundles） #

如果需要返回所有城市，无对应Bundles时总和显示为0，可使用左连接实现：

查询语法

using (var context = new YourDbContext())
{
    var cityVolumeSummary = from city in context.Cities
                           join bundle in context.Bundles.Where(b => b.IdBrand is 1 or 19)
                               on city.IdCity equals bundle.IdCity into bundleGroup
                           from bundle in bundleGroup.DefaultIfEmpty()
                           group bundle by new { city.IdCity, city.Name } into cityGroup
                           select new
                           {
                               CityId = cityGroup.Key.IdCity,
                               CityName = cityGroup.Key.Name,
                               TotalVolume = cityGroup.Sum(b => b?.Volume ?? 0)
                           };

    var result = cityVolumeSummary.ToList();
}

方法语法

using (var context = new YourDbContext())
{
    var cityVolumeSummary = context.Cities
        .GroupJoin(context.Bundles.Where(b => b.IdBrand == 1 || b.IdBrand == 19),
                   city => city.IdCity,
                   bundle => bundle.IdCity,
                   (city, bundles) => new { City = city, Bundles = bundles })
        .SelectMany(x => x.Bundles.DefaultIfEmpty(), (x, bundle) => new { x.City, Bundle = bundle })
        .GroupBy(item => new { item.City.IdCity, item.City.Name })
        .Select(group => new
        {
            CityId = group.Key.IdCity,
            CityName = group.Key.Name,
            TotalVolume = group.Sum(item => item.Bundle?.Volume ?? 0)
        });

    var result = cityVolumeSummary.ToList();
}

内容的提问来源于stack exchange，提问作者Dúver Cruz