LINQ how to query if a value is between a list of ranges?

Question

Let's say I have a Person record in a database, and there's an Age field for the person.

Now I have a page that allows me to filter for people in certain age ranges.

For example, I can choose multiple range selections, such as "0-10", "11-20", "31-40".

So in this case, I'd get back a list of people between 0 and 20, as well as 30 to 40, but not 21-30.

I've taken the age ranges and populated a List of ranges that looks like this:

class AgeRange
{ 
     int Min { get; set; }
     int Max { get; set; }
}

List<AgeRange> ageRanges = GetAgeRanges();

I am using LINQ to SQL for my database access and queries, but I can't figure out how query the ranges.

I want to do something like this, but of course, this won't work since I can't query my local values against the SQL values:

var query = from person in db.People 
            where ageRanges.Where(ages => person.Age >= ages.Min && person.Age <= ages.Max).Any())
            select person;

NotSupportedException - Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator. — Makotosan, Oct 28 '10 at 15:34
Why can't you query your local values against the db values? What I think you'll have to do here is do the query inside a loop on the sets of AgeRanges and Union the queries using Linq. I'll have to see if I can come up with an example as I've never done this before... (the union i mean) — EJC, Oct 28 '10 at 15:37
hmm, then what is `ages`? I assumed it was an instance of `AgeRange`, but that error wouldn't make sense in that case. — Kirk Woll, Oct 28 '10 at 15:39

Thomas Levesque · Accepted Answer · 2010-10-28T16:21:10.953

10

You could build the predicate dynamically with PredicateBuilder:

static Expression<Func<Person, bool>> BuildAgePredicate(IEnumerable<AgeRange> ranges)
{
    var predicate = PredicateBuilder.False<Person>();
    foreach (var r in ranges)
    {
        // To avoid capturing the loop variable
        var r2 = r;
        predicate = predicate.Or (p => p.Age >= r2.Min && p.Age <= r2.Max);
    }
    return predicate;
}

You can then use this method as follows:

var agePredicate = BuildAgePredicate(ageRanges);
var query = db.People.Where(agePredicate);

edited Oct 28 '10 at 16:21

answered Oct 28 '10 at 15:34

Thomas Levesque

286,951
70
623
758

Oh, nice. Much cleaner than what I was thinking of :) – EJC Oct 28 '10 at 15:41
When I run this it seems to only return people whose age is in the last specified date range, but no others. – diceguyd30 Oct 28 '10 at 15:53
+1 for the cleanliness though! I am definitely going to start using this! – diceguyd30 Oct 28 '10 at 16:07
Thanks for the solution. I'd like to not have to add a dependency on a third party dll if I can. I like your idea and was thinking about building my own custom expression, but am struggling in doing so. – Makotosan Oct 28 '10 at 16:10
@diceguyd30, well spotted ! I was capturing the loop variable, which is why only the last range was taken into account. It's fixed now. – Thomas Levesque Oct 28 '10 at 16:22
1

@Makotosan, you don't need to depend on a third party DLL. You can just copy the code of the PredicateBuilder class to your project, it's very short. That's actually what Jo Albahari recommands... – Thomas Levesque Oct 28 '10 at 16:24
Now that works like a charm! Change my +1 to be for cleanliness and having the best working solution! – diceguyd30 Oct 28 '10 at 17:14

diceguyd30 · Answer 2 · 2010-10-28T16:19:25.753

0

As one of your errors mentioned you can only use a local sequence with the 'Contains' method. One option would then be to create a list of all allowed ages like so:

    var ages = ageRanges
        .Aggregate(new List<int>() as IEnumerable<int>, (acc, x) => 
            acc.Union(Enumerable.Range(x.Min,x.Max - (x.Min - 1)))
        );

Then you can call:

People.Where(x => ages.Contains(x.Age))

A word of caution to this tale, should your ranges be large, then this will FAIL!

(This will work well for small ranges (your max number of accepted ages will probably never exceed 100), but any more than this and both of the above commands will become VERY expensive!)

edited Oct 28 '10 at 16:19

answered Oct 28 '10 at 16:04

diceguyd30

2,742
20
18

The problem with this solution is that it won't work well for big ranges. For example, let's say I was filtering by salary ranges instead of ages. – Makotosan Oct 28 '10 at 16:06
Indeed. I felt comfortable with this here since the range will be relatively small (100 items tops?), but you are absolutely correct. Any other domain and this could be quite the expensive operation! I'll edit with a warning. – diceguyd30 Oct 28 '10 at 16:09

score 0 · Answer 3 · answered Oct 28 '10 at 17:15

Thanks to Thomas' answer, I was able to create this more generic version that seems to be working:

   static IQueryable<T> Between<T>(this IQueryable<T> query, Expression<Func<T, decimal>> predicate, IEnumerable<NumberRange> ranges)
    {
        var exp = PredicateBuilder.False<T>();

        foreach (var range in ranges)
        {
            exp = exp.Or(
                    Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual(predicate.Body, Expression.Constant(range.Min)), predicate.Parameters))
                    .And(Expression.Lambda<Func<T, bool>>(Expression.LessThanOrEqual(predicate.Body, Expression.Constant(range.Max)), predicate.Parameters));
        }

        return query.Where(exp);
    }

score 0 · Answer 4 · answered Dec 06 '19 at 05:01

0

Much simpler implementation is to use Age.CompareTo() I had a similar problem and solved it using CompareTo In a database of houses, I want to find houses within the range max and min

from s in db.Homes.AsEnumerable() select s; houses = houses.Where( s=>s.Price.CompareTo(max) <= 0 && s.Price.CompareTo(min) >= 0 ) ;

answered Dec 06 '19 at 05:01

Sam Patirage

109
5

Where's `max` and `min` coming from? Do you only have one max and one min? In my case, I need multiple maxes and mins. For example, if I wanted houses between 100,000~200,000 as well as 300,000~400,000, but not 200,000~300,000. – Makotosan Dec 06 '19 at 13:41
Values come from controller, where you can assign min-max values based on filter selected by user. From a drop down list of enum user select price range 50,000 - 200,000 and then the controller assign min and max values and filter linq results. – Sam Patirage Dec 07 '19 at 00:11

LINQ how to query if a value is between a list of ranges?

4 Answers4

Linked