I am trying to migrate my current database from MS SQL to PostgreSQL and while the old database works I want to keep my codebase running on both databases. There is an issue with hierarchyId type which is currently migrated as text in PostgreSQL. I’ve added all missing functions that works with hierarchyID in SQL Server and also added column type mapping in EF, so it wouldn’t break during migration, but there is an issue with mapping functions for PostgreSQL.

Here is some code sample – consider that I have a column “Node” in table “Unit”.

public class ApplicationDb : DbContext
{
    public DbSet<UnitEntity> Units { get; set; }

    [DbFunction("IsDescendantOf", "public")]
    public static bool IsDescendantOf(string parentHierarchyId, string childHierarchyId)
        => throw new NotSupportedException();


    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        if (Database.IsNpgsql())
        {
            var converter = new ValueConverter<HierarchyId, string>(
                 v => v.ToString(),
                 v => HierarchyId.Parse(v));

            foreach (var prop in modelBuilder.Model.GetEntityTypes()
                             .SelectMany(t => t.GetProperties())
                             .Where(p => p.ClrType == typeof(HierarchyId)))
            {
                prop.SetColumnType("text");
                prop.SetValueConverter(converter);
            }
            
            NodeVersion.IsPostgresDb = true;
            modelBuilder.HasDbFunction(typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(IsDescendantOf), new[] { typeof(HierarchyId), typeof(string) }))
                .HasName("IsDescendantOf");

        }
    }
}

public class NodeVersion
{
    // Static property to indicate the variant being used
    public static bool IsPostgresDb { get; set; } = false; // Default to sqlserver
}

public class UnitEntity
{
    public int Id { get; set; }
    public string Name { get; set; }

    public HierarchyId Node { get; set; }
}

public class HierarchyIdToStringConverter : ValueConverter<HierarchyId, string>
{
    public HierarchyIdToStringConverter() : base(
        v => v.ToString(),
        v => HierarchyId.Parse(v))
    {
    }
}

public static class NpgsqlDbFunctionsExtensions
{
    public static bool IsDescendantOf(this HierarchyId hierarchyId, string parentValue) 
    {
        return hierarchyId.ToString().IsDescendantOf_str(parentValue);
    }

    [DbFunction("IsDescendantOf", "public")]
    public static bool IsDescendantOf_str(this string hierarchyId, string parentValue)
    {
        return hierarchyId.StartsWith(parentValue);
    }
}

public static class HierarchyIdExtensions
{
    public static IQueryable<T> WhereIsDescendantOf<T>(this IQueryable<T> query, Expression<Func<T, HierarchyId>> hierarchyIdSelector, HierarchyId parentNode)
    {
        // Extract the property access expression from the selector
        var propertyExpression = (MemberExpression)hierarchyIdSelector.Body;
        var propertyName = propertyExpression.Member.Name;

        var parameter = Expression.Parameter(typeof(T), "x");
        var propertyAccess = Expression.Property(parameter, propertyName);

        // Build the LINQ expression to mimic IsDescendantOf
        if (!NodeVersion.IsPostgresDb)
        {
            // SQL Server-specific expression using native IsDescendantOf function
            var method = typeof(HierarchyId).GetMethod("IsDescendantOf", new[] { typeof(HierarchyId) });
            var methodCall = Expression.Call(propertyAccess, method, Expression.Constant(parentNode));

            var lambda = Expression.Lambda<Func<T, bool>>(methodCall, parameter);
            return query.Where(lambda);
        }
        else
        {
            // PostgreSQL-specific expression using custom logic
            var method = typeof(NpgsqlDbFunctionsExtensions).GetMethod("IsDescendantOf");
            var methodCall = Expression.Call(null, method, propertyAccess, Expression.Constant(parentNode.ToString()));

            var lambda = Expression.Lambda<Func<T, bool>>(methodCall, parameter);
            return query.Where(lambda);
        }

        throw new NotSupportedException("Unsupported database provider.");
    }
}

public class Program 
{
    public static async Task Main(string[] args) 
    {
        var hid = HierarchyId.Parse("/5/");

        using (ApplicationDb _applicationDb = new ApplicationDb())
        {
            try
            {
                var zz = _applicationDb.Units.WhereIsDescendantOf(x => x.Node, hid).Select(a => a.Name).ToList();
            }
            catch (Exception ex)
            {
                logger.LogError(ex.ToString());
            }
        }
    }
}

So when I run this sample app on SQL Server – everything works, but on PostgreSQL I got an exception:

The parameter ‘hierarchyId’ for the DbFunction ‘HierarchyTreeTest.NpgsqlDbFunctionsExtensions.IsDescendantOf(Microsoft.EntityFrameworkCore.HierarchyId,string)’ has an invalid type ‘HierarchyId’. Ensure the parameter type can be mapped by the current provider.

I am expecting that it should work also on PostgreSQL so I wouldn’t need to make a lot of changes in my existing app.