EF6中按双列GroupBy并选择Min()及其他新增字段的实现方法

场景1：将TypeOfVehicle加入分组维度 #

如果业务要求相同RateClass + 相同TypeOfVehicle的数据才归为同一组，直接修改分组键为复合键即可：

// 分组时同时指定两个字段作为分组Key
var data = rates.GroupBy(t => new { t.RateClass, t.TypeOfVehicle })
    .Select(g => new Rates {
        RateClass = g.Key.RateClass,
        TypeOfVehicle = g.Key.TypeOfVehicle, 
        CostDay = g.Min(x => x.CostDay),
        CostWeek = g.Sum(x => x.CostWeek)
    })
    .ToList();

场景2：保持原分组维度（仅按RateClass分组），取分组内指定规则的TypeOfVehicle #

如果还是仅按RateClass分组，需要根据业务规则取分组内的TypeOfVehicle值，常见规则写法如下：

取分组内CostDay最小的那条记录对应的TypeOfVehicle #

var data = rates.GroupBy(t => t.RateClass)
    .Select(g => new Rates {
        RateClass = g.Key,
        // 先按CostDay升序排序，取第一条的TypeOfVehicle值
        TypeOfVehicle = g.OrderBy(x => x.CostDay).First().TypeOfVehicle,
        CostDay = g.Min(x => x.CostDay),
        CostWeek = g.Sum(x => x.CostWeek)
    })
    .ToList();

如果是EF Core查询（非内存集合查询），该写法在EF Core 6及以上版本可直接翻译为SQL，低版本可改用以下写法避免客户端计算：

var data = rates.GroupBy(t => t.RateClass)
    .Select(g => new {
        g.Key,
        MinCostDay = g.Min(x => x.CostDay),
        SumCostWeek = g.Sum(x => x.CostWeek)
    })
    .Join(rates, 
        g => new { g.Key, g.MinCostDay },
        r => new { r.RateClass, MinCostDay = r.CostDay },
        (g, r) => new Rates {
            RateClass = g.Key,
            TypeOfVehicle = r.TypeOfVehicle,
            CostDay = g.MinCostDay,
            CostWeek = g.SumCostWeek
        })
    .ToList();

拼接分组内所有TypeOfVehicle值 #

如果需要把同分组下所有车辆类型拼接展示，可使用：

TypeOfVehicle = string.Join(",", g.Select(x => x.TypeOfVehicle).Distinct())

内容的提问来源于stack exchange，提问作者Ben Junior