Building a parameterized EntityFramework Core Expression

Hi, I’m trying to build an Expression to get a generic entity by its primary key and getting a parameterized sql query.

Currently I can get the correct WHERE query, but it isn’t parameterized.

public async Task<TDbo> Get(TKey key, Expression<Func<TEntity, TKey>> keySelector)
{
    var propertyRef = keySelector.Body;
    var parameter = keySelector.Parameters[0];
    var constantRef = Expression.Constant(key);
    var equals = Expression.Equal(propertyRef, constantRef);
    var comparer = Expression.Lambda<Func<TEntity, bool>>(equals, parameter);

    return await _context.Set<TDbo>().SingleOrDefaultAsync(comparer);
}

This results in the following query:
SELECT e."Id", e."Name" rn FROM "People" AS ernWHERE e."Id" = 1rnLIMIT 2,
instead of the wanted:
SELECT e."Id", e."Name" rn FROM "People" AS ernWHERE e."Id" = @__s_0rnLIMIT 2

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

It’s because of Expression.Constant(key). Value constant expressions are not parameterized by the query translator. What you need is an expression referring to a property or field of another expression (which could be constant). That’s basically what C# compiler emits for closures.

One way is to actually use the C# compiler to create lambda expression with closure and take the body:

Expression<Func<TKey>> keyValue = () => key;
var variableRef = key.Body;

(the variableRef is a replacement of yours constantRef)

Another way is to use anonymous, tuple or specific class type to create explicit closure instance and bind the corresponding property or field. For instance, with anonymous type:

var variableRef = Expression.Property(Expression.Constant(new { key }), "key");

or with System.Tuple:

var variableRef = Expression.Property(Expression.Constant(Tuple.Create(key)), "Item1");

The actual method doesn’t really matter (I personally prefer the first variant with lambda) – all they will cause creating parameter by EF Core query translator.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0