如何编写可被EF Core翻译的ExecuteUpdate审计属性扩展方法？

我有多个继承自审计类AuditableEntity的实体：

public class AuditableEntity
{
    public DateTime? LastUpdated { get; set;}
}

public class Foo : AuditableEntity
{
    public int Id { get; set; }
    public string Bar { get; set; }
}

public class Bar : AuditableEntity
{
    public int Id { get; set; }
    public string Baz { get; set; }
}

我想用EF Core的.ExecuteUpdate()批量更新实体属性，包括继承来的LastUpdated字段。直接链式调用SetProperty是可行的：

public class SomeService
{
    private readonly IMyDbContext _dbContext;

    public SomeService(IMyDbContext dbContext)
    {
        _dbContext = dbContext;
    } 

    public void SomeMethod()
    {
        _dbContext.Foo
            .Where(x => x.Bar == "Hello")
            .ExecuteUpdate(setter => setter
                .SetProperty(x => x.Bar, x => "Goodbye")
                .SetProperty(x => x.LastUpdated, x => DateTime.UtcNow));
    }
}

但我想封装一个扩展方法自动处理LastUpdated更新，写了如下代码：

public static class SetPropertyCallsExtensions
{
    public static SetPropertyCalls<TSource> SetUpdateAuditableEntityProperties<TSource>(this SetPropertyCalls<TSource> setter, DateTime now)
        where TSource : AuditableEntity
    {
        return setter.SetProperty(x => x.LastUpdated, v => now);
    }
}

调用后却抛出异常：

System.InvalidOperationException: The LINQ expression 'DbSet<Foo>()
    .Where(x => x.Bar == "Hello")
    .ExecuteUpdate(setter => setter
        .SetUpdateAuditableEntityProperties(
            now: DateTime.UtcNow))' could not be translated. Additional information: The 'setPropertyCalls' argument to 'ExecuteUpdate' may only contain a chain of 'SetProperty' expressing the properties to be updated.
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   ...

方法一：封装可翻译的ExecuteUpdate扩展（推荐） #

EF Core无法解析自定义的SetPropertyCalls扩展方法，但可以通过表达式树合并的方式，把审计字段的更新逻辑和业务更新逻辑合并成EF能识别的链式调用。

代码实现：

using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Query;

public static class AuditableExecuteUpdateExtensions
{
    // 同步版本：自动添加LastUpdated更新
    public static int ExecuteUpdateWithAudit<TSource>(this IQueryable<TSource> source, Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>> setPropertyCalls)
        where TSource : AuditableEntity
    {
        // 构建审计字段更新的表达式
        var auditExpr = (Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>>)
            (setter => setter.SetProperty(x => x.LastUpdated, _ => DateTime.UtcNow));
        
        // 合并业务更新和审计更新的表达式
        var combinedExpr = CombineUpdateExpressions(setPropertyCalls, auditExpr);
        
        return source.ExecuteUpdate(combinedExpr);
    }

    // 异步版本
    public static Task<int> ExecuteUpdateWithAuditAsync<TSource>(this IQueryable<TSource> source, Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>> setPropertyCalls, CancellationToken cancellationToken = default)
        where TSource : AuditableEntity
    {
        var auditExpr = (Expression<Func<SetPropertyCalls<TSource>, SetPropertyCalls<TSource>>>)
            (setter => setter.SetProperty(x => x.LastUpdated, _ => DateTime.UtcNow));
        
        var combinedExpr = CombineUpdateExpressions(setPropertyCalls, auditExpr);
        
        return source.ExecuteUpdateAsync(combinedExpr, cancellationToken);
    }

    // 核心：合并两个SetProperty调用表达式
    private static Expression<Func<T, T>> CombineUpdateExpressions<T>(Expression<Func<T, T>> first, Expression<Func<T, T>> second)
    {
        var param = Expression.Parameter(typeof(T), "x");
        var firstBody = first.Body.Replace(first.Parameters[0], param);
        var secondBody = second.Body.Replace(second.Parameters[0], firstBody);
        return Expression.Lambda<Func<T, T>>(secondBody, param);
    }
}

// 辅助类：替换表达式中的参数
internal static class ExpressionExtensions
{
    public static Expression Replace(this Expression expression, Expression oldExpr, Expression newExpr)
    {
        return new ReplaceVisitor(oldExpr, newExpr).Visit(expression);
    }

    private class ReplaceVisitor : ExpressionVisitor
    {
        private readonly Expression _oldExpr;
        private readonly Expression _newExpr;

        public ReplaceVisitor(Expression oldExpr, Expression newExpr)
        {
            _oldExpr = oldExpr;
            _newExpr = newExpr;
        }

        public override Expression Visit(Expression node)
        {
            return node == _oldExpr ? _newExpr : base.Visit(node);
        }
    }
}

使用方式：

_dbContext.Foo
    .Where(x => x.Bar == "Hello")
    .ExecuteUpdateWithAudit(setter => setter
        .SetProperty(x => x.Bar, x => "Goodbye"));

这个方法会自动把LastUpdated = DateTime.UtcNow合并到你的业务更新逻辑中，EF Core能正常解析并生成对应的SQL。

方法二：全局拦截器（零侵入） #

如果希望所有ExecuteUpdate操作都自动更新审计字段，无需手动调用扩展，可以用EF Core的命令拦截器，在生成SQL时自动追加更新语句。

1. 定义拦截器 #

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

public class AuditUpdateInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        // 只处理UPDATE语句
        if (command.CommandText.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase))
        {
            // 提取表别名（适配EF生成的SQL格式）
            var tableAlias = GetTableAlias(command.CommandText);
            // 生成审计字段更新语句（SQL Server用GETUTCDATE()，MySQL用UTC_TIMESTAMP()，按需调整）
            var auditUpdate = $"{tableAlias}.LastUpdated = GETUTCDATE()";

            // 插入到SET子句中
            var setIndex = command.CommandText.IndexOf("SET", StringComparison.OrdinalIgnoreCase);
            if (setIndex == -1) return base.ReaderExecuting(command, eventData, result);

            var whereIndex = command.CommandText.IndexOf("WHERE", setIndex, StringComparison.OrdinalIgnoreCase);
            if (whereIndex == -1) whereIndex = command.CommandText.Length;

            var existingSet = command.CommandText.Substring(setIndex + 3, whereIndex - setIndex - 3).Trim();
            var newSet = string.IsNullOrEmpty(existingSet) ? auditUpdate : $"{existingSet}, {auditUpdate}";

            command.CommandText = command.CommandText.Substring(0, setIndex + 3) + $" {newSet}" + command.CommandText.Substring(whereIndex);
        }

        return base.ReaderExecuting(command, eventData, result);
    }

    // 提取EF生成的表别名
    private string GetTableAlias(string sql)
    {
        var fromIndex = sql.IndexOf("FROM", StringComparison.OrdinalIgnoreCase);
        if (fromIndex == -1) return "";

        var fromEnd = sql.IndexOfAny(new[] { ' ', '\n', '\r' }, fromIndex + 4);
        if (fromEnd == -1) fromEnd = sql.Length;

        var tablePart = sql.Substring(fromIndex + 4, fromEnd - fromIndex - 4).Trim();
        var aliasIndex = tablePart.IndexOf(" AS ", StringComparison.OrdinalIgnoreCase);
        return aliasIndex != -1 ? tablePart.Substring(aliasIndex + 3).Trim() : tablePart.Split('.').Last().Trim();
    }
}

2. 注册拦截器 #

在DbContext的配置中添加拦截器：

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors(new AuditUpdateInterceptor());
        // 其他数据库配置...
    }
}

这样所有ExecuteUpdate操作都会自动更新LastUpdated字段，完全不需要修改业务代码。

EF Core的ExecuteUpdate要求传入的表达式必须是直接的SetProperty链式调用，它会逐个解析这些调用生成SQL。自定义的SetPropertyCalls扩展方法对EF来说是一个黑盒，无法解析内部的SetProperty逻辑，因此抛出翻译失败的异常。

内容的提问来源于stack exchange，提问作者MorganR