c# - 如何在 EF6 中手动构造表达式以匹配相同的输出？

Question

我尝试手动构建表达式树。当 EF 无法将表达式转换为 sql 或您需要构建动态查询时，它可能会有所帮助。无论我认为这对理解幕后发生的事情有很大帮助。我试图构建简单的“where”查询。我认为这与构建的 lambda 相同。但是，sql 输出不同。

手动构建表达式:

ParameterExpression param = Expression.Parameter(typeof(Transfer));
var manualExpr = Expression.Lambda<Func<Transfer, bool>>(
    body: Expression.AndAlso(
        left: Expression.Equal(Expression.Property(param, nameof(Transfer.DestinationFromId)), Expression.Constant(transferDto.DestinationFromId, typeof(Int32))),
        right: Expression.Equal(Expression.Property(param, nameof(Transfer.DestinationToId)), Expression.Constant(transferDto.DestinationToId, typeof(Int32)))
    ), 
    parameters: param
);

var result = await DataContext.Transfers.Where(manualExpr).ToListAsync();

SQL 输出:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Time] AS [Time], 
    [Extent1].[DestinationFromId] AS [DestinationFromId], 
    [Extent1].[PassengerCount] AS [PassengerCount], 
    [Extent1].[DestinationToId] AS [DestinationToId]
    FROM [dbo].[Transfers] AS [Extent1]
    WHERE (13 = [Extent1].[DestinationFromId]) AND (11 = [Extent1].[DestinationToId])

Lambda(函数)构造表达式:

Expression<Func<Transfer, bool>> funcExpr = (t) => t.DestinationFromId == transferDto.DestinationFromId && t.DestinationToId == transferDto.DestinationToId;
var res = await DataContext.Transfers.Where(funcExpr).ToListAsync();

SQL输出

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Number] AS [Number], 
    [Extent1].[Time] AS [Time], 
    [Extent1].[DestinationFromId] AS [DestinationFromId], 
    [Extent1].[PassengerCount] AS [PassengerCount]
    [Extent1].[DestinationToId] AS [DestinationToId]
    FROM [dbo].[Transfers] AS [Extent1]
    WHERE ([Extent1].[DestinationFromId] = @p__linq__0) AND ([Extent1].[DestinationToId] = @p__linq__1)

您可以观察到 sql 中 where 语句的输出不同。手动表达式使用内联值。并且 lambda 使用属性参数，这为 SQLServer 缓存查询打开了可能性。我构建的查询错误吗？

Answer 1

要构建相同的表达式，您需要稍微更改一下代码:

var manualExpr = Expression.Lambda<Func<Transfer, bool>>(
    body: Expression.AndAlso(
        left: Expression.Equal(Expression.Property(param, nameof(Transfer.DestinationFromId)),
           // instead of using direct constant value,
           // we build expression transferDto.DestinationFromId
           Expression.PropertyOrField(Expression.Constant(transferDto), nameof(transferDto.DestinationFromId))),
        right: Expression.Equal(Expression.Property(param, nameof(Transfer.DestinationToId)),
           // instead of using direct constant value,
           // we build expression transferDto.DestinationToId
           Expression.PropertyOrField(Expression.Constant(transferDto), nameof(transferDto.DestinationToId)))
    ),
    parameters: param
);

您当前的表达方式与:

Expression<Func<Transfer, bool>> funcExpr = (t) => t.DestinationFromId == 11 && t.DestinationToId == 13)

这与您所追求的表达方式不完全相同。