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

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.

From stackoverflow

• 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

  Svish : How would that work with a whole series of Range objects?

  Marc Gravell : You could do the same with OrElse... I'll update...

  Svish : And what is this "x" in your param?

  Marc Gravell : 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 && x.Val > 2" - it simply relates to the row being processed. Maybe call it "row" if it would bother you less ;-p

  Svish : 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> as well as a Range[] ?

  Svish : and a foreach, instead of the for(int i... stuff?

  Marc Gravell : yes - foreaach would do the job... (another update coming)

  Svish : It works :D Thank you! This was fun... :D

• 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.

  Svish : How I then use the selector? In other words, what do I replace `selector(ø)` with in `p = p.Or(ø => selector(ø) >= a && selector(ø) <= b);`?

  Svish : No, I get the same `Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.` when doing that =/