Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LINQ Between Operator

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;    } } 
like image 279
andleer Avatar asked Sep 19 '09 03:09

andleer


1 Answers

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); 
like image 186
Jon Skeet Avatar answered Sep 23 '22 17:09

Jon Skeet