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.
The => operator can be used in two ways in C#: As the lambda operator in a lambda expression, it separates the input variables from the lambda body. In an expression body definition, it separates a member name from the member implementation.
Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language. Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support.
The Let keyword allows you to create a range variable and initialized with the result of the query expression and then you are allowed to use that variable with the upcoming clause in the same query.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With