Can you create a grouping based on ranges?

Question

This is related to Another Question, which I think really gets at a much simpler problem so I'm asking the simpler question here in the hopes it will help me solve the more complex one.

I would like to be able to create a grouping in a linq to sql query that groups based on a range of data within another set of data. However, i think it should work just as well in linq to objects, so let's just go with that.

Imagine you have two lists containing values

{100, 110, 120, 130, 140, 150, 160, 170}
{115, 145, 180}

Now, I would like to group the first list by the second as ranges (values that are between each group). That is, I would like a grouping like this (the 0 is implied):

{0}   {100, 110}
{115} {120, 130, 140}
{145} {150, 160, 170}
{180}

I'm almost certain i'm misusing terminology, and probably have a misunderstanding of how linq group by operator works, but if you get what I mean, I'd love some suggestions. Thanks.

Answer 1

Well, you can certainly express it in LINQ easily:

var x = from value in values
        group value by ranges.Where(x => value >= x)
                             .DefaultIfEmpty()
                             .Last();

But I very much doubt that that will work in LINQ to SQL. Basically you've got to find a simple way of mapping a value to one of those categories.

Complete example:

using System;
using System.Linq;
using System.Collections.Generic;

class Test
{
    static void Main()
    {
        int[] values = {100, 110, 120, 130, 140, 150, 160, 170};
        int[] ranges = {115, 145, 180};

        var query = from value in values
                    group value by ranges.Where(x => value >= x)
                                         .DefaultIfEmpty()
                                         .Last();

        foreach (var group in query)
        {
            Console.WriteLine("{0}: {{{1}}}", group.Key, 
                              string.Join(", ", group));
        }
    }
}

Output:

0: {100, 110}
115: {120, 130, 140}
145: {150, 160, 170}

Note that this won't include any categories which don't have any values in.

Also note that this would be simpler (using First() instead of Last()) if you'd be happy to categorize slightly differently:

115: {100, 110}
145: {120, 130, 140}
180: {150, 160, 170}

In other words, if the category was defined by the first range value higher than the row value.

EDIT: Here's a version which gives the empty groups. It's pretty horrible though, IMO:

var query = from range in ranges
            join value in values
            on range equals ranges.Where(x => value >= x)
                                  .DefaultIfEmpty()
                                  .Last() into groups
            select new { Key = range, Values = groups};

foreach (var group in query)
{
    Console.WriteLine("{0}: {{{1}}}", group.Key, 
                      string.Join(", ", group.Values));
}