C#、Linq2SQL:创建谓词以查找多个范围内的元素 [英] C#, Linq2SQL: Creating a predicate to find elements within a number of ranges

问题描述

假设我的数据库中有一个叫做 Stuff 的东西，有一个叫做 Id 的属性.从用户那里，我获得了一系列选定的 Range 对象(或者更确切地说，我从他们的输入中创建了它们)以及他们想要的 Id.该结构体的精简版本如下所示:

Lets say I have something called Stuff in my database, with a property called Id. From the user I get a sequence of selected Range objects (or rather I create them from their input) with the Ids they want. A stripped down version of that struct looks like this:

public struct Range<T> : IEquatable<Range<T>>, IEqualityComparer<Range<T>>
{
    public T A;
    public T B;
    public Range(T a, T b)
    {
        A = a;
        B = b;
    }
    ...
}

例如可以得到:

var selectedRange = new List<Range<int>>
    {
        new Range(1, 4),
        new Range(7,11),
    };

然后我想用它来创建一个谓词来只选择那些之间有值的东西.例如，使用 PredicateBuilder，我可以这样做:

I then want to use that to create a predicate to select only things which have a value between those. For example, using the PredicateBuilder, I can for example do that this way:

var predicate = PredicateBuilder.False<Stuff>();
foreach (Range<int> r in selectedRange)
{
    int a = r.A;
    int b = r.B;
    predicate = predicate.Or(ø => ø.Id >= a && ø.Id <= b);
}

然后:

var stuff = datacontext.Stuffs.Where(predicate).ToList();

哪个有效！我现在想做的是创建一个通用扩展方法来为我创建这些谓词.有点像这样:

Which works! What I would like to do now, is to create a generic extension method to create those predicates for me. Kind of like this:

public static Expression<Func<T,bool>> ToPredicate<T>(this IEnumerable<Range<int>> range, Func<T, int> selector)
{
    Expression<Func<T, bool>> p = PredicateBuilder.False<T>();
    foreach (Range<int> r in range)
    {
        int a = r.A;
        int b = r.B;
        p = p.Or(ø => selector(ø) >= a && selector(ø) <= b);
    }
    return p;
}

这里的问题是，由于 selector(ø) 调用导致 NotSupportedException 崩溃:Method 'System.Object DynamicInvoke(System.Object[])' 不支持转换为 SQL.

Problem here, is that it crashes with a NotSupportedException because of the selector(ø) call: Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.

我想这是可以理解的.但是有什么办法可以解决这个问题吗?我想要结束的是这样我就可以做:

I guess that is understandable. But is there any way to get around this? What I would like to end up with is so that I could just do:

var stuff = datacontext.Stuffs.Where(selectedRange.ToPredicate<Stuff>(ø => ø.Id));

或者更好的是，创建一些返回 IQueryable 的东西，以便我可以这样做:

Or even better, create something that returns an IQueryable so that I could just do:

var stuff = datacontext.Stuffs.WhereWithin<Stuff>(selectedRange, ø => ø.Id); // Possibly without having to specify Stuff as type there...

那么，有什么想法吗?我真的很想让这个工作，因为如果不是，我会得到很多 foreach 代码块，创建谓词......

So, any ideas? I would really like to get this working, cause if not I will get A LOT of those foreach blocks of code, creating predicates...

注 1:当然，如果我可以扩展到多个 int 会更好，比如 DateTime 等，但不确定最终如何使用 >= 和 <= 运算符... CompareTo 是否适用于 linq-to-sql?如果没有，创建两个没有问题.一个用于 int，一个用于 DateTime，因为这主要是将用于的类型.

Note 1: Of course, would be nice if I could expand to more than int, like DateTime and such, but not sure how that ends up with using the >= and <= operators... Does CompareTo work with linq-to-sql? If not there is no problem creating two. One for int and one for DateTime, since that is mostly the types this will be used for.

注意 2:它将用于报告，用户将能够根据不同的事情缩小结果的范围.就像，我想要这份关于那些人和那些日期的报告.

Note 2: It is going to be used for reporting, where the user is going to be able to narrow down what comes out, based on different things. Like, I want this report for those people and those dates.

推荐答案

与泛型一起使用是有问题的，因为 C# 不支持泛型上的运算符 - 这意味着您必须手动编写表达式.正如我们已经看到的，字符串的工作方式不同.但对于其余的，例如(未经测试)如何:

The use with generics is problematic, since C# doesn't support operators on generics - meaning you'd have to write the expression manually. And as we've already seen, string works differently. But for the rest, how about something like (untested):

(编辑多个范围)

    public static IQueryable<TSource> WhereBetween<TSource, TValue>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TValue>> selector,
        params Range<TValue>[] ranges)
    {
        return WhereBetween<TSource,TValue>(source, selector,
            (IEnumerable<Range<TValue>>) ranges);
    }

    public static IQueryable<TSource> WhereBetween<TSource, TValue>(
        this IQueryable<TSource> source,
        Expression<Func<TSource, TValue>> selector,
        IEnumerable<Range<TValue>> ranges)
    {
        var param = Expression.Parameter(typeof(TSource), "x");
        var member = Expression.Invoke(selector, param);
        Expression body = null;
        foreach(var range in ranges)
        {
            var filter = Expression.AndAlso(
                Expression.GreaterThanOrEqual(member,
                     Expression.Constant(range.A, typeof(TValue))),
                Expression.LessThanOrEqual(member,
                     Expression.Constant(range.B, typeof(TValue))));
            body = body == null ? filter : Expression.OrElse(body, filter);
        }            
        return body == null ? source : source.Where(
            Expression.Lambda<Func<TSource, bool>>(body, param));
    }

注意；Expression.Invoke 的使用意味着它可能适用于 LINQ-to-SQL 而不是 EF(目前；希望在 4.0 中修复).

Note; the use of Expression.Invoke means it will probably work on LINQ-to-SQL but not EF (at the moment; hopefully fixed in 4.0).

使用情况(在 Northwind 上测试):

With usage (tested on Northwind):

Range<decimal?> range1 = new Range<decimal?>(0,10),
                range2 = new Range<decimal?>(15,20);
var qry = ctx.Orders.WhereBetween(order => order.Freight, range1, range2);

生成 TSQL(重新格式化):

Generating TSQL (re-formatted):

SELECT -- (SNIP)
FROM [dbo].[Orders] AS [t0]
WHERE (([t0].[Freight] >= @p0) AND ([t0].[Freight] <= @p1))
OR (([t0].[Freight] >= @p2) AND ([t0].[Freight] <= @p3))

正是我们想要的;-p

这篇关于C#、Linq2SQL:创建谓词以查找多个范围内的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！