Repository navigation
Use variables instead of literals when building the "WHERE" clause #467
Description
Databases can skip the SQL parsing as well as the creation of the execution plan when bind variables are used and the same query (with different values) was already executed (what is the typical case for a grid SQL). This should make the query execution faster
See the following SC thread for details: T974081
Reacted by Andrey Bykiev, Bryan Vam, Konstantin Lazarev and houbi56
Activity
houbi56 commentedon May 16, 2023
For reference we had the same problem in our code-base for a long while.
In our app, since EntityFramework caches every expression (so that it doesn't have to re-translate to sql) it also caused memory usage to increase over time.
The root issue for use was the usage of Expression.Constant for the object value, changing to () => value causes EF to parameterize the query properly.
Expression causing non-parameterized queries:
var equalsExpression = Expression.Equal(property, Expression.Constant(kvp.Value));
Expression producing a parameterized query (EF CORE):
Expression<Func> vs = () => (T)value;
var equalsExpression = Expression.Equal(property, valueExpression);