LINQ Between Operator

Question

The following works fine with IEnumerable types, but is there any way to get something like this working with IQueryable types against a sql database?

class Program {     static void Main(string[] args)     {         var items = new[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, };          foreach (var item in items.Where(i => i.Between(2, 6)))             Console.WriteLine(item);     } }  static class Ext {    public static bool Between<T>(this T source, T low, T high) where T : IComparable    {        return source.CompareTo(low) >= 0 && source.CompareTo(high) <= 0;    } } 

Answer 1

If you express it as a where clause it may just work out of the box with LINQ to SQL, if you can construct an appropriate expression.

There may be a better way of doing this in terms of the expression trees - Marc Gravell may well be able to improve it - but it's worth a try.

static class Ext {    public static IQueryable<TSource> Between<TSource, TKey>         (this IQueryable<TSource> source,           Expression<Func<TSource, TKey>> keySelector,          TKey low, TKey high) where TKey : IComparable<TKey>    {        Expression key = Expression.Invoke(keySelector,              keySelector.Parameters.ToArray());        Expression lowerBound = Expression.GreaterThanOrEqual            (key, Expression.Constant(low));        Expression upperBound = Expression.LessThanOrEqual            (key, Expression.Constant(high));        Expression and = Expression.AndAlso(lowerBound, upperBound);        Expression<Func<TSource, bool>> lambda =             Expression.Lambda<Func<TSource, bool>>(and, keySelector.Parameters);        return source.Where(lambda);    } } 

It will probably depend on the type involved though - in particular, I've used the comparison operators rather than IComparable<T>. I suspect this is more likely to be correctly translated into SQL, but you could change it to use the CompareTo method if you want.

Invoke it like this:

var query = db.People.Between(person => person.Age, 18, 21);