LINQ Between Operator

by

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);

More Related Contents:

  1. LINQ-to-SQL vs stored procedures? [closed]
  2. Linq: adding conditions to the where clause conditionally
  3. LINQ across multiple databases
  4. Learning about LINQ [closed]
  5. How can I reject all changes in a Linq to SQL’s DataContext?
  6. LINQ with SQLite (linqtosql)
  7. How to create a dynamic LINQ join extension method
  8. LINQ not updating on .SubmitChanges()
  9. How to dynamically add OR operator to WHERE clause in LINQ
  10. LINQ To SQL exception: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains operator
  11. Linq To Sql – Dynamic OrderBy – Case When
  12. Is there a pattern using Linq to dynamically create a filter?
  13. Linq to SQL how to do “where [column] in (list of values)”
  14. Multiple WHERE clause in Linq
  15. How do I write this cross apply query in LINQ-to-SQL?
  16. Point connectionstring in dbml to app.config
  17. Building Dynamic LINQ Queries based on Combobox Value
  18. NHibernate vs LINQ to SQL
  19. Returning IEnumerable vs. IQueryable
  20. Linq to Sql: Multiple left outer joins
  21. Entity framework linq query Include() multiple children entities
  22. System.LINQ.Dynamic: Select(” new (…)”) into a List (or any other enumerable collection of )
  23. Standard Deviation in LINQ
  24. How To Project a Line Number Into Linq Query Results
  25. Calculate difference from previous item with LINQ
  26. Linq query with nullable sum
  27. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  28. Last and LastOrDefault not supported
  29. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  30. LINQ: How to convert the nested hierarchical object to flatten object

Leave a Comment