构建参数化的 EntityFramework 核心表达式 [英] Building a parameterized EntityFramework Core Expression

问题描述

您好，我正在尝试构建一个表达式以通过其主键获取通用实体并获取参数化的 sql 查询.

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

目前我可以得到正确的 WHERE 查询，但它没有参数化.

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);
}

这会导致以下查询:SELECT e."Id", e."Name" FROM "People" AS e WHERE e."Id" = 1 LIMIT 2,而不是通缉:SELECT e."Id", e."Name" FROM "People" AS e WHERE e."Id" = @__s_0 LIMIT 2

This results in the following query: SELECT e."Id", e."Name" FROM "People" AS e WHERE e."Id" = 1 LIMIT 2, instead of the wanted: SELECT e."Id", e."Name" FROM "People" AS e WHERE e."Id" = @__s_0 LIMIT 2

推荐答案

这是因为Expression.Constant(key).值常量表达式不被查询翻译器参数化.您需要的是引用另一个表达式的属性或字段的表达式(可以 是常量).这基本上就是 C# 编译器为闭包发出的内容.

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.

一种方法是实际使用 C# 编译器创建带闭包的 lambda 表达式并获取主体:

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;

(variableRef 是你的 constantRef 的替代品)

(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");

或使用 System.Tuple:

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

实际的方法并不重要(我个人更喜欢第一个带有 lambda 的变体) - 所有这些都会导致 EF Core 查询转换器创建参数.

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.

这篇关于构建参数化的 EntityFramework 核心表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！