EF Core迁移SQL Server到PostgreSQL时HierarchyId转文本的函数映射问题

问题根源 #

你遇到的异常是因为PostgreSQL环境下，EF Core尝试将HierarchyId类型参数传入映射到text列的函数，类型不匹配导致的。需要针对不同数据库提供者，分别注册适配的函数映射，并实现对应的SQL逻辑。

分步解决方案 #

1. 配置DbContext的条件映射 #

在OnModelCreating中，根据当前数据库提供者，注册不同版本的IsDescendantOf函数，并配置Node列的类型映射：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置Unit表Node列的类型映射
    var nodeColumnType = Database.ProviderName switch
    {
        "Microsoft.EntityFrameworkCore.SqlServer" => "hierarchyid",
        "Npgsql.EntityFrameworkCore.PostgreSQL" => "text",
        _ => throw new NotSupportedException("Unsupported database provider")
    };
    modelBuilder.Entity<Unit>()
        .Property(u => u.Node)
        .HasColumnType(nodeColumnType);

    // 注册SQL Server原生IsDescendantOf函数
    if (Database.ProviderName == "Microsoft.EntityFrameworkCore.SqlServer")
    {
        modelBuilder.HasDbFunction(typeof(SqlServerDbFunctionsExtensions).GetMethod(
                nameof(SqlServerDbFunctionsExtensions.IsDescendantOf),
                new[] { typeof(HierarchyId), typeof(HierarchyId) }))
            .HasName("IsDescendantOf")
            .HasSchema("dbo");
    }
    // 注册PostgreSQL自定义函数映射
    else if (Database.ProviderName == "Npgsql.EntityFrameworkCore.PostgreSQL")
    {
        modelBuilder.HasDbFunction(typeof(CustomDbFunctions).GetMethod(
                nameof(CustomDbFunctions.IsDescendantOf),
                new[] { typeof(string), typeof(string) }))
            .HasName("is_descendant_of")
            .HasSchema("public");
    }
}

2. 创建PostgreSQL自定义函数 #

通过EF Core迁移或直接在PostgreSQL中创建实现IsDescendantOf逻辑的函数，模拟SQL Server HierarchyId的行为：

CREATE OR REPLACE FUNCTION public.is_descendant_of(child_path text, parent_path text)
RETURNS boolean AS $$
BEGIN
    -- 处理空路径场景
    IF parent_path IS NULL THEN
        RETURN child_path IS NULL;
    END IF;
    -- 统一路径格式，避免部分匹配错误（如/1和/10）
    child_path = '/' || trim(child_path, '/') || '/';
    parent_path = '/' || trim(parent_path, '/') || '/';
    -- 判断子路径是否以父路径为前缀
    RETURN starts_with(child_path, parent_path);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

3. 封装通用扩展方法 #

创建通用扩展方法，让业务代码无需修改，自动适配不同数据库：

public static class CustomDbFunctions
{
    // 供EF Core映射到PostgreSQL自定义函数的方法（仅用于查询翻译）
    public static bool IsDescendantOf(this string node, string parentNode)
        => throw new InvalidOperationException("该方法仅用于EF Core查询翻译，不可直接调用");

    // 封装HierarchyId扩展，自动适配数据库类型
    public static bool IsDescendantOf(this HierarchyId node, HierarchyId parentNode)
    {
        var currentContext = CurrentDbContext.Get();
        if (currentContext.Database.ProviderName == "Npgsql.EntityFrameworkCore.PostgreSQL")
        {
            // 转换为字符串调用PostgreSQL函数
            return IsDescendantOf(node.ToString(), parentNode.ToString());
        }
        // SQL Server直接调用原生方法
        return SqlServerDbFunctionsExtensions.IsDescendantOf(node, parentNode);
    }
}

4. 业务代码保持不变 #

原来的查询代码无需修改，依然使用HierarchyId类型调用：

var parentNode = HierarchyId.Parse("/1/");
var childUnits = dbContext.Units.Where(u => u.Node.IsDescendantOf(parentNode)).ToList();

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