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;

Answer 1

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);

Answer 2

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!)

Answer 3

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