Group By variable ceiling value with catch-all maximum using LINQ To NHibernate

Question

I'd like to construct a LINQ GroupBy statement according to the following decimal categories: 0-50, 50-100, 100-250, 250-above. I found Group by variable integer range using Linq which discusses how to use a variable range, but that query has a finite upper bound. My query needs to be able to handle everything over 250 as one group. I tried using decimal.maxValue as my upper bound, but the query couldn't handle it, I suppose because the value is larger than what NHibernate can handle. Ideally, I'd like to do this without specifying a max value so the query is independent of the database.

Edit: I'm pretty sure I can do this by using a array of floor values and following the pattern in the link. But I'm curious if there is a kind of catch-all group-by construct.

Answer 1

Edit:

You changed your OP to state that you could use a floor function, but you wanted to find out about a default grouping.

Mathematically a floor function is equivalent. In the case of ceiling, the lower bound for the data they used is presumably 0. In the case of floor, the logical upper bound is positive infinity (effectively it ends up being the highest value the DB supports, since integers don't support the concept of infinity). It gets you where you want to go.

If you want something that might be more applicable to other situations, you could try something like this:

items.GroupBy(item =>
    (
        floors.FirstOrDefault(floor => floor <= item)
            ?? "Default"
        )
        .ToString()
    );

It probably wouldn't work in Linq to NHibernate, as I don't think this would map well to SQL. Instead you could import the whole set into memory first (.ToList()), and then add your grouping as a Linq to Objects query.

It doesn't make a lot of sense to use it in this situation, but it might in the case of non-number-line groupings:

var groups = new HashSet<string>
{
    "Orange",
    "Green",
    "Mauve",
};

items.GroupBy(item =>
    groups.Contains(item.Color)
        ? item.Color
        : "Default"
    );

Before Edit:

You could simply reverse the logic and you'll automatically include everything below a certain value.

var floors = new[] { 250, 100, 50, 0 };
var groupings = items.GroupBy(item => floors.First(floor => floor <= item));

How it works:

• Take an item 270.
  The first item in the list would be the first bucket it falls under. This is because 250 <= 270.

• Take an item 99.
  The third item in the list would be the first bucket it falls under. 250 is not less than 99. 100 is not less than 99. But 50 is less than 99.

• An item 50 would fall into the third bucket.
  It is less than 250 and 100, but equal to 50.

Doesn't quite match the description in your question:

Your group description is a bit broken. You'd have to bucketize them separately for this algorithm to work. There would be a 0-50 bucket, 51-100 bucket, etc. Or a 0-49 bucket, a 50-99 bucket, etc.

A 0-50 bucket, and a 50-100 bucket couldn't exist together.