40

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;
   }
}
andleer
  • 22,388
  • 8
  • 62
  • 82

1 Answers1

55

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);
ddechant
  • 151
  • 2
  • 4
  • 14
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • 1
    Nice. This also made me understand Linq expressions a bit more. – Dykam Sep 19 '09 at 09:53
  • Jon, Sorry I haven't accepted this. Been under the weather for a few days. Question: you write about using comparison operators instead of IComparable. What would that look like? What would any of this look like as an IEnumerable? I need to play with this and hope to do that today. Thanks much! – andleer Sep 24 '09 at 16:43
  • Using `IComparable` you'd need expression trees to invoke CompareTo twice. All doable, but a bit of a pain. Not sure what you mean about "What would any of this look like as an `IEnumerable`?" - can you elaborate? – Jon Skeet Sep 24 '09 at 16:56
  • 2
    I also think this is an excellent example of code that would be more readable using the `var` keyword. – tvanfosson Feb 25 '10 at 20:26
  • This is very cool. Might I suggest an optional `inclusive` parameter? – Justin Morgan - On strike Feb 28 '11 at 21:20
  • @Justin: You'd need an inclusive top, and an inclusive bottom... ranges get tricky :) But yes, depending on the situation you may want that. – Jon Skeet Feb 28 '11 at 22:13
  • Is this correct? When you define the `lowerBound` expression, it shouldn't be `Expression.GreaterThanOrEqual(Expression.Constant(low), key);`? – Adriano Machado Jan 03 '12 at 10:13
  • Wouldn't this be a performance hit compared to writing out `Where(i => low <= i && i <= high)`? Could that be mitigated--maybe by setting it up to emit an expression beforehand, and passing that to `Where` as needed? Would the prettier syntax be worth it? – Justin Morgan - On strike Jun 20 '12 at 19:32
  • @JustinMorgan: No, I wouldn't expect this to be a performance hit at all. I'd expect the query translation to handle it, and even if *that* didn't, the query optimizer in SQL Server should come up with the same query plan. – Jon Skeet Jun 20 '12 at 19:40
  • I was thinking of a hit on the app server, since it has to construct an expression tree. But I suppose that would have to happen anyway in order to pass the logic down to the database. I may be prematurely optimizing; I'm not super familiar with expression tree performance (and I confess I don't remember what I meant by emitting an expression beforehand). – Justin Morgan - On strike Nov 07 '12 at 15:26
  • Why not change `this` to type of `IEnumerable` and in your return statement `source.AsQueryable().Where(lambda)`? – Doug Chamberlain Apr 09 '13 at 15:40
  • @MVCylon: I'd rather the caller did that explicitly, if they want to. If you've only got an `IEnumerable`, you'd be better off using simple delegates. – Jon Skeet Apr 09 '13 at 15:44
  • I know this is an old answer, but I've stumbled across it. Can you explain the reason we need to do this: `Expression key = Expression.Invoke(keySelector, keySelector.Parameters.ToArray());`. According to MSDN, `Invoke` simply creates an invocation Expression with the supplied parameters. But that's what `keySelector` already is, and we're passing the same parameters anyway. Can't we just remove `key` here and use `keySelector` instead? – Rob Oct 14 '15 at 04:31
  • @Rob: Hmm... I honestly couldn't tell you off-hand. I suspect I decompiled what the C# compiler does with this. I'd have to experiment a bit, but I suspect that the difference is that you only want to invoke the key selector once and then use the result twice. In other words, you want `{ var key = keySelector(p); return low <=key && key <= high; }` rather than `{ return low <= keySelector(p) && keySelector(p) <= high; }` but I'm not actually sure whether what I've got has that effect anyway. (Slightly jetlagged...) – Jon Skeet Oct 14 '15 at 12:37
  • @JonSkeet Cheers mate, that makes sense - I'll have a bit of a dig around to see if it is indeed invoking the selector just once – Rob Oct 14 '15 at 23:24