C#, Linq2SQL: Creating a predicate to find elements within a number of ranges

Question

Lets say I have something called Stuff in my database, with a property called Id. From the user I get a sequence of selected Range objects (or rather I create them from their input) with the Ids they want. A stripped down version of that struct looks like this:

public struct Range<T> : IEquatable<Range<T>>, IEqualityComparer<Range<T>>
{
    public T A;
    public T B;
    public Range(T a, T b)
    {
        A = a;
        B = b;
    }
    ...
}

So one could for example have gotten:

var selectedRange = new List<Range<int>>
    {
        new Range(1, 4),
        new Range(7,11),
    };

I then want to use that to create a predicate to select only things which have a value between those. For example, using the PredicateBuilder, I can for example do that this way:

var predicate = PredicateBuilder.False<Stuff>();
foreach (Range<int> r in selectedRange)
{
    int a = r.A;
    int b = r.B;
    predicate = predicate.Or(ø => ø.Id >= a && ø.Id <= b);
}

and then:

var stuff = datacontext.Stuffs.Where(predicate).ToList();

Which works! What I would like to do now, is to create a generic extension method to create those predicates for me. Kind of like this:

public static Expression<Func<T,bool>> ToPredicate<T>(this IEnumerable<Range<int>> range, Func<T, int> selector)
{
    Expression<Func<T, bool>> p = PredicateBuilder.False<T>();
    foreach (Range<int> r in range)
    {
        int a = r.A;
        int b = r.B;
        p = p.Or(ø => selector(ø) >= a && selector(ø) <= b);
    }
    return p;
}

Problem here, is that it crashes with a NotSupportedException because of the selector(ø) call: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

I guess that is understandable. But is there any way to get around this? What I would like to end up with is so that I could just do:

var stuff = datacontext.Stuffs.Where(selectedRange.ToPredicate<Stuff>(ø => ø.Id));

Or even better, create something that returns an IQueryable so that I could just do:

var stuff = datacontext.Stuffs.WhereWithin<Stuff>(selectedRange, ø => ø.Id); // Possibly without having to specify Stuff as type there...

So, any ideas? I would really like to get this working, cause if not I will get A LOT of those foreach blocks of code, creating predicates...

---

Note 1: Of course, would be nice if I could expand to more than int, like DateTime and such, but not sure how that ends up with using the >= and <= operators... Does CompareTo work with linq-to-sql? If not there is no problem creating two. One for int and one for DateTime, since that is mostly the types this will be used for.

Note 2: It is going to be used for reporting, where the user is going to be able to narrow down what comes out, based on different things. Like, I want this report for those people and those dates.

Answer 1

The use with generics is problematic, since C# doesn't support operators on generics - meaning you'd have to write the expression manually. And as we've already seen, string works differently. But for the rest, how about something like (untested):

(edited for multiple ranges)

    public static IQueryable<TSource> WhereBetween<TSource, TValue>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TValue>> selector,
        params Range<TValue>[] ranges)
    {
        return WhereBetween<TSource,TValue>(source, selector,
            (IEnumerable<Range<TValue>>) ranges);
    }

    public static IQueryable<TSource> WhereBetween<TSource, TValue>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TValue>> selector,
        IEnumerable<Range<TValue>> ranges)
    {
        var param = Expression.Parameter(typeof(TSource), "x");
        var member = Expression.Invoke(selector, param);
        Expression body = null;
        foreach(var range in ranges)
        {
            var filter = Expression.AndAlso(
                Expression.GreaterThanOrEqual(member,
                     Expression.Constant(range.A, typeof(TValue))),
                Expression.LessThanOrEqual(member,
                     Expression.Constant(range.B, typeof(TValue))));
            body = body == null ? filter : Expression.OrElse(body, filter);
        }            
        return body == null ? source : source.Where(
            Expression.Lambda<Func<TSource, bool>>(body, param));
    }

Note; the use of Expression.Invoke means it will probably work on LINQ-to-SQL but not EF (at the moment; hopefully fixed in 4.0).

With usage (tested on Northwind):

Range<decimal?> range1 = new Range<decimal?>(0,10),
                range2 = new Range<decimal?>(15,20);
var qry = ctx.Orders.WhereBetween(order => order.Freight, range1, range2);

Generating TSQL (re-formatted):

SELECT -- (SNIP)
FROM [dbo].[Orders] AS [t0]
WHERE (([t0].[Freight] >= @p0) AND ([t0].[Freight] <= @p1))
OR (([t0].[Freight] >= @p2) AND ([t0].[Freight] <= @p3))

Just what we wanted ;-p

Answer 2

You are getting that error, because everything for LINQ to SQL needs to be in the form of an Expression. Try this

public static Expression<Func<T,bool>> ToPredicate<T>(
    this IEnumerable<Range<int>> range, 
    Expression<Func<T, int>> selector
) {
    Expression<Func<T, bool>> p = PredicateBuilder.False<T>();
    Func<T, int> selectorFunc = selector.Compile();
    foreach (Range<int> r in range)
    {
        int a = r.A;
        int b = r.B;
        p = p.Or(ø => selectorFunc(ø) >= a && selectorFunc(ø) <= b);
    }
    return p;
}

Notice that I compile the selector before using it. This should work with out a hitch, I have used something like it in the past.