Building a parameterized EntityFramework Core Expression

Question

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\" \r\n FROM \"People\" AS e\r\nWHERE e.\"Id\" = 1\r\nLIMIT 2, instead of the wanted: SELECT e.\"Id\", e.\"Name\" \r\n FROM \"People\" AS e\r\nWHERE e.\"Id\" = @__s_0\r\nLIMIT 2

Answer 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.