如何在C# Linq中按多列分组，且按列的数值范围分组

需求说明 #

原分组逻辑是按School、Friend、FavoriteColor和单个SchoolYear值进行分组，现在需要调整为按连续的SchoolYear范围分组，规则如下：

• 以基准年份为中心，将[基准-1, 基准+1]范围内的年份归为同一组
• 各组范围不能重叠，若后续年份超出当前组的范围，则以该年份为新基准创建分组
• 若存在年份间隔（比如从4跳至7），则新基准年份对应的范围为[7-1,7+1]

实现思路 #

要实现这种动态范围分组，核心是先为每个Child计算出其所属的分组基准值，再将这个基准值和原有的School、Friend、FavoriteColor一起作为分组键，最终完成分组。

具体步骤：

1. 先按School、Friend、FavoriteColor进行初步分组，确保同一基础属性组内的Child才会按年份范围合并
2. 在每个基础属性组内，按SchoolYear排序，遍历并为每个Child分配对应的基准年份
3. 基于包含基准年份的组合键进行最终分组，构造ConsolidatedChild对象

代码实现 #

首先定义所需的实体类：

public class Child
{
    public string School { get; set; }
    public string FavoriteColor { get; set; }
    public string Friend { get; set; }
    public string Name { get; set; }
    public int SchoolYear { get; set; }
}

public class ConsolidatedChild
{
    public string School { get; set; }
    public string FavoriteColor { get; set; }
    public string Friend { get; set; }
    public int SchoolYear { get; set; } // 代表分组的基准年份
    public List<Child> Children { get; set; }
}

然后实现分组逻辑：

var list = new List<Child>()
{
    new Child() {School = "School1", FavoriteColor = "blue", Friend = "Bob", Name = "John", SchoolYear = 1},
    new Child() {School = "School1", FavoriteColor = "blue", Friend = "Bob", Name = "Pete", SchoolYear = 2},
    new Child() {School = "School1", FavoriteColor = "blue", Friend = "Bob", Name = "Fred", SchoolYear = 2},
    new Child() {School = "School2", FavoriteColor = "blue", Friend = "Fred", Name = "Bob", SchoolYear = 5},
};

// 先按基础属性分组，再为每个子组内的Child分配基准年份
var withGroupKey = list
    .GroupBy(x => new { x.School, x.Friend, x.FavoriteColor })
    .SelectMany(g => 
    {
        var sortedChildren = g.OrderBy(x => x.SchoolYear).ToList();
        if (!sortedChildren.Any()) return Enumerable.Empty<(Child Child, int GroupBaseYear)>();
        
        // 初始化第一个分组的基准年份
        var currentBase = sortedChildren[0].SchoolYear;
        var result = new List<(Child, int)>();
        
        foreach (var child in sortedChildren)
        {
            // 如果当前年份超出当前基准的±1范围，更新基准
            if (child.SchoolYear < currentBase - 1 || child.SchoolYear > currentBase + 1)
            {
                currentBase = child.SchoolYear;
            }
            result.Add((child, currentBase));
        }
        return result;
    });

// 按组合键分组，构造最终结果
var newList = withGroupKey
    .GroupBy(x => new { x.Child.School, x.Child.Friend, x.Child.FavoriteColor, x.GroupBaseYear })
    .Select(y => new ConsolidatedChild()
    {
        FavoriteColor = y.Key.FavoriteColor,
        Friend = y.Key.Friend,
        School = y.Key.School,
        SchoolYear = y.Key.GroupBaseYear,
        Children = y.Select(x => x.Child).ToList()
    });

测试代码及结果 #

运行以下测试代码：

foreach(var item in newList)
{
    Console.WriteLine($"School: {item.School} FavouriteColor: {item.FavoriteColor} Friend: {item.Friend} SchoolYear: {item.SchoolYear}");
    foreach(var child in item.Children)
    {
        Console.WriteLine($"    Name: {child.Name}");
    }
}

输出结果与期望一致：

School: School1 FavouriteColor: blue Friend: Bob SchoolYear: 1
    Name: John
    Name: Pete
    Name: Fred
School: School2 FavouriteColor: blue Friend: Fred SchoolYear: 5
    Name: Bob

补充说明 #

如果需要处理更复杂的年份间隔场景（比如从4跳至7），上述逻辑依然适用：当遇到7时，因为它和之前的基准（比如4）的范围[3,5]不重叠，会自动将7设为新基准，对应的范围是[6,8]，所有落在这个范围内的年份都会被分到该组。

内容的提问来源于stack exchange，提问作者Xeno Xetric