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