LINQ Between statement

Question

The following steps work fine with IEnumerable types, but is there a 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 can just work out of the box with LINQ to SQL if you can build the appropriate expression.

Perhaps the best way to do this in terms of expression trees is that Mark Gravell may well 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); } } 

This will probably depend on what type is involved, in particular, I used comparison operators, not IComparable<T> . I suspect that this will most likely be correctly translated into SQL, but you can change it to use the CompareTo method if you want.

Call it like this:

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