C#中,LINQ2SQL:是否可以连接两个queryables成一个? [英] C#, Linq2Sql: Is it possible to concatenate two queryables into one?
问题描述
我有一个可查询在这里我使用了各种的 其中,
和的 WhereBetween
报表向下缩小收集到一定组。现在的我需要添加样的凡|| WhereBetween
。换句话说,我不能只是把它们连在一起,就像我到现在,因为那将工作作为和。所以,我怎么能做到这一点。
我看到两种可能性:
- 创建两个queryables 使用一个我,一个
其中,
,以及一个使用WhereBetween
。 ,然后将它们连接起来。不知道这是否可能?此外,虽然没有在我的具体情况,你最有可能重复... - 不知何故,最终的合并
其中,
表达,并在WhereBetween
带有某种或
第一,如前所述,我不知道,甚至有可能。而如果是,我不是那么肯定是做的好方法。
第二,我可以看到作为一个选项,但不能完全肯定所有的细节。下面是我的其他问题,我现在用的是 WhereBetween
方法,它的伟大工程:
公共静态的IQueryable< TSource> WhereBetween< TSource,TValue>(
本的IQueryable< TSource>源,
表达式来; Func键< TSource,TValue>>选择器,
的IEnumerable<范围和LT; TValue>>范围)
{
变种参数= Expression.Parameter(typeof运算(TSource),×);
VAR成员= Expression.Invoke(选择,参数);
表达体= NULL;
的foreach(在范围VAR范围)
{
VAR过滤= Expression.AndAlso(
Expression.GreaterThanOrEqual(成员,
Expression.Constant(range.A的typeof (TValue))),
Expression.LessThanOrEqual(成员,
Expression.Constant(range.B的typeof(TValue))));
=人体身体== NULL?过滤:Expression.OrElse(车身,过滤器);
}
返回身体== NULL?来源:source.Where(
Expression.Lambda<&Func键LT; TSource,布尔>>(身体参数));
}
我在想,我也许可以提取它的表达建筑物部成的新方法。也许是这样的:
公共静态的IQueryable< TSource> WhereBetween< TSource,TValue>(
本的IQueryable< TSource>源,
表达式来; Func键< TSource,TValue>>选择器,
的IEnumerable<范围和LT; TValue>>范围)
{
返回source.Where(WhereBetween(选择,范围));
}
公共静态表达式来; Func键< TSource,布尔>> WhereBetween< TSource,TValue>(
表达式来; Func键< TSource,TValue>>选择器,
的IEnumerable<范围和LT; TValue>>范围)
{
VAR参数=表达式。参数(typeof运算(TSource),×);
VAR成员= Expression.Invoke(选择,参数);
表达体= NULL;
的foreach(在范围VAR范围)
{
VAR过滤= Expression.AndAlso(
Expression.GreaterThanOrEqual(成员,
Expression.Constant(range.A的typeof (TValue))),
Expression.LessThanOrEqual(成员,
Expression.Constant(range.B的typeof(TValue))));
=人体身体== NULL?过滤:Expression.OrElse(车身,过滤器);
}
返回身体== NULL
? O =>真正的
:Expression.Lambda<&Func键LT; TSource,布尔>>(机身,参数);
}
然后我可以用新的方法来得到表达,而不是可查询。
您这里有两个选择 - Queryable.Union
,或表达式组合。我一般倒是倾向于后者,通过 OrElse运算
- 这(使用LINQ到SQL至少)你可以用2的表达做(见下文) - 但在这两种情况下,它应该得到组成:
使用(VAR CTX =新DataClasses1DataContext())
{
CTX。登录= Console.Out;
表达式来; Func键<客户,布尔>> LHS =
X => x.Country ==UK;
表达式来; Func键<客户,布尔>> RHS =
X => x.ContactName.StartsWith(A);
VAR ARR1 = ctx.Customers.Where(
lhs.OrElse(右))ToArray的()。
VAR ARR2 = ctx.Customers.Where(左)
.Union(ctx.Customers.Where(右))ToArray的()。
}
两者 ARR1
和 ARR2
每次只能执行1数据库命中(虽然TSQL不同;第一个或
在 WHERE
条款;第二与 UNION
)两个单独的查询
这是我使用的扩展方法:
静态表达式来; Func键< T,BOOL>> OrElse运算< T>(
这个表达式来; Func键< T,BOOL>> LHS,
表达式来; Func键< T,BOOL>>右)
{
无功行= Expression.Parameter(typeof运算(T),行);
变种体= Expression.OrElse(
Expression.Invoke(左,行),
Expression.Invoke(右,行));
返回Expression.Lambda<&Func键LT; T,BOOL>>(体,行);
}
I have one queryable where I have used various Where
and WhereBetween
statements to narrow the collection down to a certain set. Now I need to add kind of a Where || WhereBetween
. In other words, I can't just chain them together like I have up till now, cause that will work as an And. So, how can I do this?
I see two possibilities:
- Create two queryables from the one I have, one using the
Where
, and one usingWhereBetween
. And then concatenate them. Don't know if this is even possible? Also, although not in my particular case, you would most likely end up with duplicates... - Somehow merge the
Where
expression and the expression created in theWhereBetween
with some sort of Or.
The first, as mentioned, I am not sure is even possible. And if it was, I'm not so sure it is a good way to do it.
The second, I can see as an option, but not totally sure about all the details. Below is the WhereBetween
method from my other question, which I now use and it works great:
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));
}
I'm thinking that I could maybe extract the expression building portion of it into a new method. Perhaps like this:
public static IQueryable<TSource> WhereBetween<TSource, TValue>(
this IQueryable<TSource> source,
Expression<Func<TSource, TValue>> selector,
IEnumerable<Range<TValue>> ranges)
{
return source.Where(WhereBetween(selector, ranges));
}
public static Expression<Func<TSource, bool>> WhereBetween<TSource, TValue>(
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
? ø => true
: Expression.Lambda<Func<TSource, bool>>(body, param);
}
I could then use that new method to get the expression instead of the queryable.
You have two options here - Queryable.Union
, or expression combination. I'd generally favor the latter, via OrElse
- which (with LINQ-to-SQL at least) you can do with 2 expressions (see below) - but in either case it should get composed:
using(var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out;
Expression<Func<Customer, bool>> lhs =
x => x.Country == "UK";
Expression<Func<Customer, bool>> rhs =
x => x.ContactName.StartsWith("A");
var arr1 = ctx.Customers.Where(
lhs.OrElse(rhs)).ToArray();
var arr2 = ctx.Customers.Where(lhs)
.Union(ctx.Customers.Where(rhs)).ToArray();
}
Both arr1
and arr2
each only perform 1 database hit (although the TSQL is different; the first has an OR
in the WHERE
clause; the second has two separate queries with UNION
).
Here's the extension method I used:
static Expression<Func<T, bool>> OrElse<T>(
this Expression<Func<T, bool>> lhs,
Expression<Func<T, bool>> rhs)
{
var row = Expression.Parameter(typeof(T), "row");
var body = Expression.OrElse(
Expression.Invoke(lhs, row),
Expression.Invoke(rhs, row));
return Expression.Lambda<Func<T, bool>>(body, row);
}
这篇关于C#中,LINQ2SQL:是否可以连接两个queryables成一个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!