Linq:动态查询构造:查询移至客户端 [英] Linq: Dynamic Query Contruction: query moves to client-side
问题描述
我一直很感兴趣地关注这里的对话:
I've been following with great interest the converstaion here:
关于构建甚至表名都是动态的表达式树.
with regards to constructing expression trees where even the table name is dynamic.
为此，我创建了一个扩展方法addWhere，它看起来像:
Toward that end, I've created a Extension method, addWhere, that looks like:
static public IQueryable<TResult> addWhere<TResult>(this IQueryable<TResult> query, string columnName, string value)
{
var providerType = query.Provider.GetType();
// Find the specific type parameter (the T in IQueryable<T>)
var iqueryableT = providerType.FindInterfaces((ty, obj) => ty.IsGenericType && ty.GetGenericTypeDefinition() == typeof(IQueryable<>), null).FirstOrDefault();
var tableType = iqueryableT.GetGenericArguments()[0];
var tableName = tableType.Name;
var tableParam = Expression.Parameter(tableType, tableName);
var columnExpression = Expression.Equal(
Expression.Property(tableParam, columnName),
Expression.Constant(value));
var predicate = Expression.Lambda(columnExpression, tableParam);
var function = (Func<TResult, Boolean>)predicate.Compile();
var whereRes = query.Where(function);
var newquery = whereRes.AsQueryable();
return newquery;
}
[感谢Timwi提供的代码基础]
[thanks to Timwi for the basis of that code]
在功能上可行.
我可以打电话给
query = query.addWhere("CurUnitType", "ML 15521.1");
，它在功能上等同于:
query = query.Where(l => l.CurUnitType.Equals("ML 15521.1"));
即返回的行相同.
但是，我开始观看sql日志，并注意到以下内容:
However, I started watching the sql log, and I noticed with the line:
query = query.Where(l => l.CurUnitType.Equals("ML 15521.1"));
生成的查询是:
SELECT (A bunch of columns)
FROM [dbo].[ObjCurLocView] AS [t0]
WHERE [t0].[CurUnitType] = @p0
而当我使用线路时
query = query.addWhere("CurUnitType", "ML 15521.1");
生成的查询是:
SELECT (the same bunch of columns)
FROM [dbo].[ObjCurLocView] AS [t0]
因此，现在比较是在客户端进行的，而不是添加到sql中.
So, the comparison is now happening on the client side, instead of being added to the sql.
显然，这不是很热.
说实话，我主要是从Timwi的(略有不同)示例中剪切并粘贴了addWhere代码，所以其中有些超出了我的脑海.我想知道是否可以对此代码进行任何调整，因此将表达式转换为SQL语句，而不是确定为客户端
To be honest, I mostly cut-and-pasted the addWhere code from Timwi's (slightly different) example, so some of it is over my head. I'm wondering if there's any adjustment I can make to this code, so the expression is converted into the SQL statement, instead of being determined client-side
感谢您抽出宝贵的时间阅读本文档，我欢迎任何可以帮助我的意见，解决方案，链接等.当然，如果我通过其他方式找到解决方案，我将在此处发布答案.
Thanks for taking the time to read through this, I welcome any comments, solutions, links, etc, that could help me with this. And of course if I find the solution through other means, I'll post the answer here.
干杯.
推荐答案
最大的问题是您正在将表达式树转换为委托.查看 Queryable.Where 的签名-它是在表达式树中表示的，不是代表.因此，您实际上是在呼叫 Enumerable.Where 代替.这就是为什么您需要在之后调用AsQueryable的原因-但这在这里没有足够的魔力.它实际上并没有放回内部仅表达树"的领域，因为您仍然可以在那里获得委托.现在，它已包裹在表达式树中，但是您已经丢失了内部发生的事情的详细信息.
The big problem is that you're converting the expression tree into a delegate. Look at the signature of Queryable.Where - it's expressed in expression trees, not delegates. So you're actually calling Enumerable.Where instead. That's why you need to call AsQueryable afterwards - but that doesn't do enough magic here. It doesn't really put it back into "just expression trees internally" land, because you've still got the delegate in there. It's now wrapped in an expression tree, but you've lost the details of what's going on inside.
我怀疑您想要的是什么
var predicate = Expression.Lambda<Func<TResult, Boolean>>
(columnExpression, tableParam);
return query.Where(predicate);
我很容易承认我没有阅读其余的代码，因此可能还会发生其他事情……但这是核心.您需要一个强类型的表达式树(因此可以调用Queryable.Where中.试一试:)
I readily admit that I haven't read the rest of your code, so there may be other things going on... but that's the core bit. You want a strongly typed expression tree (hence the call to the generic form of Expression.Lambda) which you can then pass into Queryable.Where. Give it a shot :)
这篇关于Linq:动态查询构造:查询移至客户端的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
