How to declare a Linq Expression variable in order to have it processed as a dbParameter

Question

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.

I find your question somewhat confusing. If you want to dynamically add `AND` logic conditions, just declare an `IQueryable` and dynamically append `.Where(i => yourPredicate)`. See: http://stackoverflow.com/questions/11189826/c-sharp-dynamic-linq-variable-where-clause If you want to apply `OR` logic you will need an approach such as this: http://www.albahari.com/nutshell/predicatebuilder.aspx — andleer, Jun 12 '14 at 15:31
The 'or' and 'and' expressions work just fine. The problem reside on the binding of parameters. Maybe I should edit the question and remove the end. Which is just for context. — Pascal Amey, Jun 12 '14 at 15:37

score 10 · Accepted Answer · answered Jun 12 '14 at 15:54

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);

How to declare a Linq Expression variable in order to have it processed as a dbParameter

1 Answers1

Linked