LINQ操作员之间

问题描述

以下对于IEnumerable类型可以很好地工作，但是有什么方法可以针对SQL数据库使用类似IQueryable的类型呢?

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

推荐答案

如果将其表示为where子句，则可能可以直接使用LINQ to SQL，如果可以的话构造一个合适的表达式.

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.

就表达式树而言，可能有更好的方法-Marc Gravell可能会对其进行改进-但值得一试.

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

但这可能取决于所涉及的类型-特别是，我使用了比较运算符而不是IComparable<T>.我怀疑这更可能正确地转换为SQL，但是您可以根据需要将其更改为使用CompareTo方法.

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.

像这样调用它:

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

这篇关于LINQ操作员之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！