EF Core 7中自定义SqlExpressionFactory的替代方案咨询

背景 #

我们的数据库采用两张表存储可翻译数据：一张存储默认语言数据（例如Product表），另一张以Translation为后缀存储翻译数据（例如ProductTranslation表）。之前开发了一个EF Core扩展，可自动将单表查询替换为左连接翻译表的子查询，实现多语言数据的自动加载。

原查询代码：

context.Product
    .Where(t => t.Description.Contains('free'))
    .OrderBy(t => t.Name)
    .ToList();

正常生成的SQL：

SELECT Id, Name, Description
FROM Product
WHERE Description like 'free'
ORDER BY Name

扩展后期望生成的SQL：

SELECT Id, Name, Description
FROM (
    SELECT o.Id, COALESCE(t.Name, o.Name) AS Name, COALESCE(t.Description, o.Description) AS Description
    FROM Product as o
    LEFT JOIN ProductTranslation as t on t.Id = o.Id and t.LanguageId = 4
) as d
WHERE Description like 'free'
ORDER BY Name

原有实现的问题 #

之前的方案是重写SqlExpressionFactory的Select方法，并通过IDbContextOptionsExtension替换默认服务：

internal sealed class TranslationSqlExpressionFactory : SqlExpressionFactory
{
    public override SelectExpression Select(IEntityType entityType)
    {
        if (IsTranslatable(entityType))
        {
            var query = TranslationRawSql(entityType);
            var fromSqlExpression = new FromSqlExpression(entityType.GetTableMappings().Single().Table, query, Expression.Constant(new object[0]));
            return base.Select(entityType, fromSqlExpression);
        }
        return base.Select(entityType);
    }
}

internal sealed class TranslationOptionsExtension : IDbContextOptionsExtension
{
    public void ApplyServices(IServiceCollection services)
    {
        services.AddTransient<ISqlExpressionFactory, TranslationSqlExpressionFactory>();
    }
}

但EF Core 7开始，各数据库提供商拥有专属的ISqlExpressionFactory实现（如SqlServerSqlExpressionFactory、SqliteSqlExpressionFactory），直接替换会覆盖提供商的特定逻辑，引发功能冲突。目前临时方案是为每个提供商编写对应子类，但维护成本极高。

更适配的替代方案 #

1. 装饰器模式包装提供商实现 #

不直接替换服务，而是用装饰器包裹原有提供商的ISqlExpressionFactory实现，仅在需要时拦截Select方法：

internal sealed class TranslationSqlExpressionFactoryDecorator : ISqlExpressionFactory
{
    private readonly ISqlExpressionFactory _innerFactory;

    public TranslationSqlExpressionFactoryDecorator(ISqlExpressionFactory innerFactory)
    {
        _innerFactory = innerFactory;
    }

    // 重写Select方法，注入翻译逻辑
    public SelectExpression Select(IEntityType entityType)
    {
        if (!IsTranslatable(entityType))
            return _innerFactory.Select(entityType);

        var baseSelect = _innerFactory.Select(entityType);
        var entityTable = baseSelect.Tables.Single() as TableExpression;
        if (entityTable == null) return baseSelect;

        var translationEntityType = GetTranslationEntityType(entityType);
        var translationTable = _innerFactory.Table(translationEntityType);

        // 构建JOIN条件：ID匹配 + 当前语言ID
        var joinCondition = _innerFactory.Equal(
            _innerFactory.Property(entityTable, entityType.FindPrimaryKey().Properties.Single()),
            _innerFactory.Property(translationTable, translationEntityType.FindProperty("Id")))
            .And(_innerFactory.Equal(
                _innerFactory.Property(translationTable, translationEntityType.FindProperty("LanguageId")),
                _innerFactory.Constant(GetCurrentLanguageId())));

        // 添加左连接
        baseSelect.AddJoin(JoinExpressionType.LeftOuter, translationTable, joinCondition);

        // 替换列：用COALESCE取翻译值或默认值
        foreach (var property in entityType.GetProperties())
        {
            var translationProperty = translationEntityType.FindProperty(property.Name);
            if (translationProperty == null) continue;

            var columnExpression = _innerFactory.Column(entityTable, property);
            var translationColumn = _innerFactory.Column(translationTable, translationProperty);
            var coalesceExpression = _innerFactory.Coalesce(translationColumn, columnExpression);

            baseSelect.ReplaceProjection(columnExpression, coalesceExpression);
        }

        return baseSelect;
    }

    // 其他ISqlExpressionFactory接口方法直接委托给_innerFactory
    public SqlExpression Add(SqlExpression left, SqlExpression right, RelationalTypeMapping typeMapping = null)
        => _innerFactory.Add(left, right, typeMapping);

    // ... 其余接口方法同理实现
}

在TranslationOptionsExtension中注册装饰器：

public void ApplyServices(IServiceCollection services)
{
    services.AddTransient<ISqlExpressionFactory>(sp =>
    {
        var providerFactory = sp.GetRequiredKeyedService<ISqlExpressionFactory>(sp.GetRequiredService<IDatabaseProvider>().Name);
        return new TranslationSqlExpressionFactoryDecorator(providerFactory);
    });
}

该方案保留提供商原生实现，仅注入翻译逻辑，无需为每个提供商编写子类。

2. 利用EF Core查询拦截器 #

通过IQueryExpressionInterceptor拦截查询表达式，修改查询树来添加翻译表左连接：

internal sealed class TranslationQueryInterceptor : IQueryExpressionInterceptor
{
    public Expression QueryCompilationStarting(Expression queryExpression, QueryExpressionInterceptorContext context)
    {
        return new TranslationExpressionVisitor(context).Visit(queryExpression);
    }

    private class TranslationExpressionVisitor : ExpressionVisitor
    {
        private readonly QueryExpressionInterceptorContext _context;

        public TranslationExpressionVisitor(QueryExpressionInterceptorContext context)
        {
            _context = context;
        }

        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(Queryable) && node.Arguments.Count > 0)
            {
                var source = node.Arguments[0];
                if (source is ConstantExpression constant && constant.Value is IQueryable queryable)
                {
                    var entityType = _context.Model.FindEntityType(queryable.ElementType);
                    if (entityType != null && IsTranslatable(entityType))
                    {
                        var translatedQuery = BuildTranslatedQuery(queryable, entityType, _context);
                        var newSource = Expression.Constant(translatedQuery);
                        var newArguments = new List<Expression> { newSource };
                        newArguments.AddRange(node.Arguments.Skip(1));
                        return Expression.Call(node.Method, newArguments);
                    }
                }
            }
            return base.VisitMethodCall(node);
        }

        private IQueryable BuildTranslatedQuery(IQueryable source, IEntityType entityType, QueryExpressionInterceptorContext context)
        {
            var dbContext = context.Context;
            var translationEntityType = GetTranslationEntityType(entityType);
            var translationDbSet = dbContext.GetType().GetProperty(translationEntityType.ClrType.Name)?.GetValue(dbContext) as IQueryable;

            if (translationDbSet == null) return source;

            var parameterO = Expression.Parameter(entityType.ClrType, "o");
            var parameterT = Expression.Parameter(translationEntityType.ClrType, "t");

            var idPropertyO = Expression.Property(parameterO, entityType.FindPrimaryKey().Properties.Single().Name);
            var idPropertyT = Expression.Property(parameterT, translationEntityType.FindProperty("Id").Name);
            var languageIdPropertyT = Expression.Property(parameterT, translationEntityType.FindProperty("LanguageId").Name);
            var languageIdConstant = Expression.Constant(GetCurrentLanguageId());

            var joinCondition = Expression.AndAlso(
                Expression.Equal(idPropertyO, idPropertyT),
                Expression.Equal(languageIdPropertyT, languageIdConstant));

            // 构建投影：对可翻译属性使用COALESCE
            var projectionProperties = new List<MemberBinding>();
            foreach (var property in entityType.GetProperties())
            {
                var translationProperty = translationEntityType.FindProperty(property.Name);
                if (translationProperty == null)
                {
                    projectionProperties.Add(Expression.Bind(property.PropertyInfo, Expression.Property(parameterO, property.Name)));
                    continue;
                }

                var propertyO = Expression.Property(parameterO, property.Name);
                var propertyT = Expression.Property(parameterT, translationProperty.Name);
                var coalesce = Expression.Coalesce(propertyT, propertyO);
                projectionProperties.Add(Expression.Bind(property.PropertyInfo, coalesce));
            }

            var projection = Expression.MemberInit(Expression.New(entityType.ClrType), projectionProperties);

            // 动态构建左连接查询表达式
            var query = source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable),
                    "GroupJoin",
                    new[] { entityType.ClrType, translationEntityType.ClrType, typeof(object) },
                    source.Expression,
                    translationDbSet.Expression,
                    Expression.Lambda(idPropertyO, parameterO),
                    Expression.Lambda(idPropertyT, parameterT),
                    Expression.Lambda(
                        Expression.Call(
                            typeof(Enumerable),
                            "DefaultIfEmpty",
                            new[] { translationEntityType.ClrType },
                            Expression.Parameter(typeof(IEnumerable<>).MakeGenericType(translationEntityType.ClrType), "tj")),
                        Expression.Parameter(typeof(IEnumerable<>).MakeGenericType(translationEntityType.ClrType), "tj")))
                .Call(
                    typeof(Queryable),
                    "SelectMany",
                    new[] { entityType.ClrType, translationEntityType.ClrType, entityType.ClrType },
                    Expression.Parameter(typeof(IQueryable<>).MakeGenericType(typeof(IGrouping<,>).MakeGenericType(typeof(object), translationEntityType.ClrType))),
                    Expression.Lambda(projection, parameterO, parameterT)));

            return query;
        }
    }
}

在DbContext中注册拦截器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new TranslationQueryInterceptor());
}

该方案侵入性低，直接修改查询树，兼容性最好，无需关注提供商差异。

3. 实体映射到翻译视图 #

将原实体映射到包含左连接逻辑的数据库视图，结合参数传递实现多语言查询：

首先创建数据库视图（或动态生成）：

CREATE VIEW ProductTranslated AS
SELECT o.Id, COALESCE(t.Name, o.Name) AS Name, COALESCE(t.Description, o.Description) AS Description
FROM Product o
LEFT JOIN ProductTranslation t ON t.Id = o.Id AND t.LanguageId = @LanguageId

在EF Core中配置实体映射：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>()
        .ToView("ProductTranslated")
        .HasQueryFilter(p => EF.Property<int>(p, "LanguageId") == GetCurrentLanguageId());
}

该方案适合简单场景，动态语言切换需结合DbCommandInterceptor替换参数。

总结 #

• 装饰器模式：灵活度高，保留提供商原生逻辑，适合深度定制SQL生成的场景。
• 查询拦截器：侵入性低，兼容性强，无需关注提供商差异，推荐优先使用。
• 视图映射：适合静态语言场景，依赖数据库视图，动态切换需额外处理。

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