views:

1696

answers:

2

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.

+4  A: 

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

Marc Gravell
How would that work with a whole series of Range<TValue> objects?
Svish
You could do the same with OrElse... I'll update...
Marc Gravell
And what is this "x" in your param?
Svish
An expression parameter needs to be named. If we had written the lambda by hand, it would be the "x" in "x => x.Val < 1 -p
Marc Gravell
aha. cool. no, no, I just didn't know what it was for :p Would what you now changed it with work with an IEnumerable<Range<TValue>> as well as a Range<TValue>[] ?
Svish
and a foreach, instead of the for(int i... stuff?
Svish
yes - foreaach would do the job... (another update coming)
Marc Gravell
It works :D Thank you! This was fun... :D
Svish
A: 

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.

Nick Berardi
How I then use the selector? In other words, what do I replace `selector(ø)` with in `p = p.Or(ø => selector(ø) >= a `?
Svish
No, I get the same `Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.` when doing that =/
Svish