C＃，Linq2SQL：创建谓词以查找多个范围内的元素 [英] C#, Linq2SQL: Creating a predicate to find elements within a number of ranges
问题描述
可以说我的数据库中有一些名为Stuff的东西，名为Id。从用户中，我得到了一系列选定的Range对象（或者我从他们的输入中创建它们）和他们想要的ID。该结构的精简版本如下所示：
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>>
{
新范围（1，4），
新范围（7,11），
};
然后我想用它创建一个谓词来选择只包含那些值的东西。例如，使用 PredicateBuilder ，我可以这样做：
var predicate = PredicateBuilder.False< Stuff>（）;
foreach（在selectedRange中的范围< int> r）
{
int a = r.A;
int b = r.B;
predicate = predicate.Or（ø=>ø.Id> = a&ø.Id< = b）;
}
然后：
var stuff = datacontext.Stuffs.Where（predicate）.ToList（）;
哪些方法可行！我现在想要做的是创建一个通用的扩展方法来为我创建谓词。像这样：
public static Expression< Func< T，bool>> ToPredicate< T>（这个IEnumerable< Range< int>>范围，Func< T，int>选择符）
{
表达式< Func< T，bool>> p = PredicateBuilder.False< T>（）;
foreach（范围 r在范围内）
{
int a = r.A;
int b = r.B;
p = p.Or（ø=>选择器（ø）> = a&&选择器（ø）< = b）;
}
return p;
$ b 这里的问题是由于选择符（ø）而导致NotSupportedException异常）调用：方法'System.Object DynamicInvoke（System.Object []）'不支持对SQL的转换。
<我想这是可以理解的。但是有什么办法可以解决这个问题吗？我想结束的是我可以这样做：
var stuff = datacontext.Stuffs.Where（selectedRange .ToPredicate< Stuff>（ø=>ø.Id））;
甚至更好，创建一个返回IQueryable的东西，以便我可以这样做：
var stuff = datacontext.Stuffs.WhereWithin< Stuff>（selectedRange，ø=>ø.Id）; //可能不需要指定Stuff作为类型...
那么，有什么想法？我真的想要得到这个工作，因为如果没有，我会得到很多这些foreach代码块，创建谓词......
注1：当然，如果我可以扩展到比int更多的类型，比如DateTime等，但是不确定最终如何使用> =和< =运算符... CompareTo与linq-to-sql一起工作吗？如果没有，创建两个没有问题。一个用于int，另一个用于DateTime，因为这主要是用于这种类型的。
注2：将会是用于报告，其中用户将能够根据不同的事情来缩小出现的内容。就像我想为这些人和那些日期报告。
支持泛型的运算符 - 意味着您必须手动编写表达式。正如我们已经看到的，字符串的工作方式不同。但对于其他人来说，怎么样（未经测试）：
$ b （编辑适用于多个范围）
public static IQueryable< TSource> WhereBetween< TSource，TValue>（
this IQueryable< TSource> source，
Expression< Func< TSource，TValue>> selector，
params Range< TValue> [] ranges）$ b $ （源，选择器，
（IEnumerable< Range< TValue>>）范围）; b {
返回WhereBetween< TSource，TValue>
}
public static IQueryable< TSource> WhereBetween< TSource，TValue>（
this IQueryable< TSource> source，
Expression< Func< TSource，TValue>> selector，
IEnumerable< Range< TValue> ranges） $ b {
var param = Expression.Parameter（typeof（TSource），x）;
var member = Expression.Invoke（selector，param）;
Expression body = null;
foreach（范围内的变量范围）
{
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？过滤器：Expression.OrElse（body，filter）;
}
返回正文== null？ source：source.Where（
Expression.Lambda< Func< TSource，bool>>（body，param））;
}
注意;使用Expression.Invoke意味着它可能在LINQ-to-SQL上工作，但不是EF（目前，希望在4.0中修复）。
使用（测试过的在Northwind上）：
范围< decimal？>范围1 =新范围<小数？>（0,10），
范围2 =新范围<小数？>（15,20）;
var qry = ctx.Orders.WhereBetween（order => order.Freight，range1，range2）;
生成TSQL（重新格式化）：
< （订单）AS [t0]
WHERE（（[t0]。[Freight]> = [code $] （[t0]。（运费）> = @ p2）AND（[t0]。[运费]< = 0 p1））
OR = @ p3））
正是我们想要的;-p
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;
}
...
}
So one could for example have gotten:
var selectedRange = new List<Range<int>>
{
new Range(1, 4),
new Range(7,11),
};
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);
}
and then:
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;
}
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));
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...
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...
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.
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.
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):
(edited for multiple ranges)
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));
}
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).
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);
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))
Just what we wanted ;-p
这篇关于C＃，Linq2SQL：创建谓词以查找多个范围内的元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
