EF Core 7 自定义CTE扩展方法如何转换为SQL？

你已经完成了CTE扩展方法和自定义SQL表达式的基础定义，接下来需要通过EF Core的查询翻译体系，把自定义方法调用转换成对应的SQL表达式，最终生成合法的CTE语句，具体步骤如下：

1. 完善自定义SQL表达式（补全CTE别名支持） #

你的CteSqlDefinitionExpression缺少CTE别名（生成合法CTE SQL的必要项），先修改该类：

namespace CTE.EntityFrameworkCore.Query.Expressions;

public class CteSqlDefinitionExpression : SqlExpression
{
    public string Alias { get; }
    public SqlExpression Definition { get; }
    public CteSqlDefinitionExpression? Parent { get; private set; }
    public CteSqlDefinitionExpression? Root
    {
        get
        {
            var root = Parent;
            while (root?.Parent != null)
            {
                root = root.Parent;
            }
            return root;
        }
    }

    public override bool CanReduce => false;

    // 修改构造函数，新增Alias参数
    public CteSqlDefinitionExpression(string alias, SqlExpression definition, RelationalTypeMapping? typeMapping, CteSqlDefinitionExpression? parent = null)
         : base(definition.Type, typeMapping)
    {
        Alias = alias;
        Definition = definition;
        Parent = parent;
    }

    public CteSqlExpression ToCteExpression()
    {
        List<CteSqlDefinitionExpression> definitions = new();
        var parent = this;

        while (parent != null)
        {
            definitions.Add(parent);
            parent = parent.Parent;
        }

        definitions.Reverse();
        return new CteSqlExpression(definitions, TypeMapping);
    }

    protected override Expression VisitChildren(ExpressionVisitor visitor)
    {
        var visitedParent = Parent == null ? null : (CteSqlDefinitionExpression)visitor.Visit(Parent);
        var visitedDefinition = (SqlExpression)visitor.Visit(Definition);
        if (ReferenceEquals(Parent, visitedParent) && ReferenceEquals(Definition, visitedDefinition))
            return this;
        return new CteSqlDefinitionExpression(Alias, visitedDefinition, TypeMapping, visitedParent);
    }

    // 完善Print方法，输出CTE别名与定义结构
    protected override void Print(ExpressionPrinter expressionPrinter)
    {
        expressionPrinter.Append(Alias);
        expressionPrinter.Append("\n AS \n(");
        expressionPrinter.Visit(Definition);
        expressionPrinter.Append(")");
    }

    public override bool Equals(object? obj)
        => base.Equals(obj) && obj is CteSqlDefinitionExpression node && Equals(node);

    private bool Equals([NotNull] CteSqlDefinitionExpression node)
        => Alias == node.Alias && Equals(Parent, node.Parent) && Definition.Equals(node.Definition);

    public override int GetHashCode()
    {
        var hash = new HashCode();
        hash.Add(base.GetHashCode());
        hash.Add(Alias);
        hash.Add(Parent == null ? 0 : Parent.GetHashCode());
        hash.Add(Definition.GetHashCode());
        return hash.ToHashCode();
    }
}

2. 实现自定义方法调用翻译器 #

创建CteMethodCallTranslator，将AsCte/ThenAs方法调用转换成自定义SQL表达式：

using System.Linq.Expressions;
using System.Reflection;
using CTE.Extensions;
using CTE.EntityFrameworkCore.Query.Expressions;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;

namespace CTE.EntityFrameworkCore.Query.Translation;

public class CteMethodCallTranslator : IMethodCallTranslator
{
    private readonly ISqlExpressionFactory _sqlExpressionFactory;
    private readonly MethodInfo _asCteMethod = CteQueryableExtensions.asCteMethod;
    private readonly MethodInfo _thenAsMethod = CteQueryableExtensions.thenAsMethod;

    public CteMethodCallTranslator(ISqlExpressionFactory sqlExpressionFactory)
    {
        _sqlExpressionFactory = sqlExpressionFactory;
    }

    public SqlExpression? Translate(SqlExpression? instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments)
    {
        // 处理AsCte方法
        if (method.IsGenericMethod && method.GetGenericMethodDefinition() == _asCteMethod)
        {
            var definition = arguments[0];
            // 生成默认CTE别名，也可扩展为用户指定别名的重载
            var alias = $"CTE_{definition.Type.Name}";
            return new CteSqlDefinitionExpression(alias, definition, definition.TypeMapping);
        }

        // 处理ThenAs链式CTE方法
        if (method.IsGenericMethod && method.GetGenericMethodDefinition() == _thenAsMethod)
        {
            var source = arguments[0] as CteSqlDefinitionExpression;
            var definitionLambda = arguments[1] as LambdaExpression;
            if (source == null || definitionLambda == null) return null;

            // 替换Lambda参数，解析出CTE定义查询
            var parameter = definitionLambda.Parameters[0];
            var substituted = ReplacingExpressionVisitor.Replace(parameter, source, definitionLambda.Body);
            var definition = _sqlExpressionFactory.ApplyDefaultTypeMapping(substituted as SqlExpression);
            
            var alias = $"CTE_{definition.Type.Name}";
            return new CteSqlDefinitionExpression(alias, definition, definition.TypeMapping, source);
        }

        return null;
    }
}

3. 注册自定义翻译组件 #

创建自定义翻译器提供者，并注册到EF Core服务体系：

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;
using CTE.EntityFrameworkCore.Query.Translation;

public class YourDbContext : DbContext
{
    // 你的DbSet定义
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString") // 根据数据库类型调整，如UseNpgsql
                      .ReplaceService<IMethodCallTranslatorProvider, CteMethodCallTranslatorProvider>()
                      .ReplaceService<IQueryTranslationPostprocessor, CteQueryTranslationPostprocessor>();
    }
}

// 自定义翻译器提供者，添加CTE翻译器
public class CteMethodCallTranslatorProvider : RelationalMethodCallTranslatorProvider
{
    public CteMethodCallTranslatorProvider(RelationalMethodCallTranslatorProviderDependencies dependencies)
        : base(dependencies)
    {
        AddTranslator(new CteMethodCallTranslator(dependencies.SqlExpressionFactory));
    }
}

4. 实现查询后处理器，整合CTE到SQL头部 #

EF Core需要将CTE放在SELECT语句最前面，因此需要自定义后处理器调整查询结构：

using Microsoft.EntityFrameworkCore.Query;
using CTE.EntityFrameworkCore.Query.Expressions;

public class CteQueryTranslationPostprocessor : RelationalQueryTranslationPostprocessor
{
    public CteQueryTranslationPostprocessor(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies)
        : base(dependencies, relationalDependencies)
    {
    }

    public override Expression Process(Expression query)
    {
        query = base.Process(query);
        
        var visitor = new CteExpressionVisitor();
        var visitedQuery = visitor.Visit(query);
        
        // 如果找到CTE定义，将其包裹到查询最外层
        if (visitor.FoundCteDefinition != null)
        {
            var cteExpression = visitor.FoundCteDefinition.ToCteExpression();
            return new SelectExpression(
                cteExpression.TypeMapping,
                new[] { cteExpression },
                visitedQuery as SelectExpression);
        }
        
        return visitedQuery;
    }

    private class CteExpressionVisitor : ExpressionVisitor
    {
        public CteSqlDefinitionExpression? FoundCteDefinition { get; private set; }

        protected override Expression VisitExtension(Expression extensionExpression)
        {
            if (extensionExpression is CteSqlDefinitionExpression cteDef)
            {
                FoundCteDefinition = cteDef;
                return cteDef.Definition;
            }
            return base.VisitExtension(extensionExpression);
        }
    }
}

5. 测试CTE查询 #

现在可以直接使用你的扩展方法编写CTE查询：

var adultUsersCte = dbContext.Users
    .Where(u => u.Age > 18)
    .AsCte();

var filteredUsers = adultUsersCte
    .ThenAs(cte => cte.Where(u => u.Name.StartsWith("A")))
    .Select(u => new { u.Id, u.Name });

var results = filteredUsers.ToList();

生成的SQL会自动包含WITH CTE结构：

WITH CTE_User
AS
(
    SELECT [u].[Id], [u].[Name], [u].[Age]
    FROM [Users] AS [u]
    WHERE [u].[Age] > 18
),
CTE_User
AS
(
    SELECT [cte].[Id], [cte].[Name], [cte].[Age]
    FROM [CTE_User] AS [cte]
    WHERE [cte].[Name] LIKE N'A%'
)
SELECT [cte].[Id], [cte].[Name]
FROM [CTE_User] AS [cte]

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