How to force SQL parameter instead of constant in Expression predicate

Question

I want to create predicate expression so that it translates into WHERE clause with SQL parameter, e.g.:

WHERE e.Id = @id_1;

I have this code

int id = 1;
Expression<Func<Entity, int>> keySelector = e => e.Id;
BinaryExpression equals = Expression.Equal(
   keySelector.Body, 
   Expression.Constant(id, keySelector.Body.Type)); //replace this with a variable

var predicate = Expression.Lambda<Func<Entity, bool>>(equals, keySelector.Parameters[0]);

but it translates into:

WHERE e.Id = 1;

which generates new query execution plan for each id.

Does EF core provide some internal mechanism, that I could (should) use, e.g. special subclass of Expression, or ExpressionVisitor?

Ivan Stoev · Accepted Answer · 2022-02-07 13:39:16Z

4

Does EF core provide some internal mechanism, that I could (should) use, e.g. special subclass of Expression, or ExpressionVisitor?

Nothing special. All you need is to emulate C# closure. This could be done in several ways - using anonymous type, concrete type, Tuple or even real C# compiler generated closure like

int id = 1;
Expression<Func<int>> valueSelector = () => id;
var value = valueSelector.Body; // use this in place of Expression.Constant

Example of anonymous type approach

int id = 1;
var variable = new { id };
var value = Expression.Property(Expression.Constant(variable), nameof(id));

Similar with new Tuple<int>(id) and Item1.

answered Feb 7, 2022 at 13:39

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to force SQL parameter instead of constant in Expression predicate

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related