如何声明一个Linq Expression变量，以便将其作为dbParameter进行处理 [英] How to declare a Linq Expression variable in order to have it processed as a dbParameter

问题描述

我正在尝试针对Entity框架（或其他Linq提供商）创建动态查询。

让我用一些示例代码解释我的问题。

如果我硬编码一个表达式：

  var id = 12345; 
 
表达式< Func< ItemSearch，bool>>> myLambda =（s）=> s.Id == id; 
 
 var finalQuery = context.ItemSearch.Where（myLambda）; 
 this.Log（（finalQuery as ObjectQuery）.ToTraceString（））; 
  

生成的SQL如下所示：

  SELECT ... 
 FROM ViewItemSearchExtent1
 WHEREExtent1.ID =：p__linq__0 
  

与一个不错的：p__linq__0 dbParameter。

如果我创建一个表达式：

  var id = 12345; 
 ParameterExpression param = Expression.Parameter（typeof（ItemSearch），s）; 
表达式prop = Expression.Property（param，Id）; 
表达式val = Expression.Constant（id）; 
表达式searchExpr = Expression.Equal（prop，val）; 
 
表达式< Func< ItemSearch，bool>>> myLambda = 
 Expression.Lambda&FunC< ItemSearch，bool>>（searchExpr，param）; 
 
 var finalQuery = context.ItemSearch.Where（myLambda）; 
 this.Log（（finalQuery as ObjectQuery）.ToTraceString（））; 
  

生成的SQL如下所示：

  SELECT ... 
 FROM ViewItemSearchExtent1
 WHEREExtent1.ID = 12345 
  

没有更多的：p__linq__0 dbParameter，所以Db引擎不能缓存查询计划。我知道这是因为我使用

  Expression val = Expression.Constant（id）; 
  

但是我不知道如何绑定一个变量而不是值。

解决方案

您需要在编译时创建一个lambda来关闭 id 变量，然后您可以在其中使用更复杂的lambda来构建使用体：

  var id = 12345 ; 
 ParameterExpression param = Expression.Parameter（typeof（ItemSearch），s）; 
表达式prop = Expression.Property（param，Id）; 
表达式< Func< int>> idLambda =（）=> ID; 
表达式searchExpr = Expression.Equal（prop，idLambda.Body）; 
 
表达式< Func< ItemSearch，bool>>> myLambda = 
 Expression.Lambda&FunC< ItemSearch，bool>>（searchExpr，param）; 
  

I'm trying to create dynamic queries against Entity framework (or other Linq provider).

Let me explain my problem with some sample code.

If I hardcode an expression :

var id = 12345;

Expression<Func<ItemSearch, bool>> myLambda = (s) => s.Id == id;

var finalQuery = context.ItemSearch.Where(myLambda);
this.Log((finalQuery as ObjectQuery).ToTraceString());

The generated SQL looks like this :

SELECT ...
FROM ViewItemSearch "Extent1"
WHERE "Extent1".ID = :p__linq__0

with a nice :p__linq__0 dbParameter.

If I create an expression :

var id = 12345;
ParameterExpression param = Expression.Parameter(typeof(ItemSearch), "s");
Expression prop = Expression.Property(param, "Id");
Expression val = Expression.Constant(id);
Expression searchExpr = Expression.Equal(prop, val);

Expression<Func<ItemSearch, bool>> myLambda =  
    Expression.Lambda<Func<ItemSearch, bool>>(searchExpr , param);

var finalQuery = context.ItemSearch.Where(myLambda);
this.Log((finalQuery as ObjectQuery).ToTraceString());

The generated SQL looks like this :

SELECT ...
FROM ViewItemSearch "Extent1"
WHERE "Extent1".ID = 12345

No more :p__linq__0 dbParameter so the Db engine cannot cache query plans.

I understand that it is because I use

Expression val = Expression.Constant(id);

But I can't figure out how to bind a variable instead of the value.

解决方案

You'll need to create a lambda at compile time to close over the id variable, to which you can then grab the body of an use in the composition of your more complex lambda:

var id = 12345;
ParameterExpression param = Expression.Parameter(typeof(ItemSearch), "s");
Expression prop = Expression.Property(param, "Id");
Expression<Func<int>> idLambda = () => id;
Expression searchExpr = Expression.Equal(prop, idLambda.Body);

Expression<Func<ItemSearch, bool>> myLambda =
    Expression.Lambda<Func<ItemSearch, bool>>(searchExpr, param);

这篇关于如何声明一个Linq Expression变量，以便将其作为dbParameter进行处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！