How to group by multiple columns, with a column grouping within a range of values in C# Linq

Question

To help understand the question, context is given from an answer (Courtesy of the answer from @jamiec) on a similar question asked previously.

Given a list:

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

My initial query would look like:

var newList = list
    .GroupBy(x => new {x.School, x.Friend, x.FavoriteColor, x.SchoolYear})
    .Select(y => new ConsolidatedChild()
        {
            FavoriteColor = y.Key.FavoriteColor,
            Friend = y.Key.Friend,
            School = y.Key.School,
            SchoolYear = y.Key.SchoolYear
            Children = y.ToList()
        }
    );

and below is test code:

foreach(var item in newList)
{
    Console.WriteLine("School: {0} FavouriteColor: {1} Friend: {2} SchoolYear: {3}", item.School,item.FavoriteColor,item.Friend, item.SchoolYear);
    foreach(var child in item.Children)
    {
        Console.WriteLine("\t Name: {0}", child.Name);
    }
}

The result for the above code would look like this:

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

In my example I want to group by a range of SchoolYears. I'd want to find the range of SchoolYear values using the original list. If the SchoolYear were 1, then I want to group any value of SchoolYear where it is 0 or 1 or 2 into the same group. If the SchoolYear was 3, then I want to group values of SchoolYear that are 3 or 4 since 2 has been taken by the previous group.

If there is a large gap in the dataset where the next SchoolYear goes from 4 to 7, then I want to group values of SchoolYear that are 6 or 7 or 8. Essentially, SchoolYear +/- 1 depending on if the number is not taken by a previous group. Is this possible and how would you do it?

The result I am looking for is this:

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

score 1 · Answer 1 · answered Jul 29 '23 at 07:57

use this :

    static int SchoolYearGroup(int y)
                => y switch
                {
                    (<= 2) => 1,
                    _ => 2
                };
    var newLit = list
                    .GroupBy(x => new { 
x.School, 
x.Friend, 
x.FavoriteColor, 
SchoolYear = SchoolYearGroup(x.SchoolYear) })
                    .Select(y => new ConsolidatedChild()
                    {
                        FavoriteColor = y.Key.FavoriteColor,
                        Friend = y.Key.Friend,
                        School = y.Key.School,
                        SchoolYear = y.Key.SchoolYear,
                        Children = y.ToList()
                    })

How to group by multiple columns, with a column grouping within a range of values in C# Linq

1 Answers1