parameters type mapping to the user-defined function

[tsardaryanCamenAI]

I'm trying to emulate hierarchyID functionality in postgresql while we are migrating from SQL server to PostgreSQL and during the migration hierarchyID type is migrated to text fields (I know about ltree). So I've created function in postgresql for IsDescendentOf and mapped my defined function in c# code as

public bool IsDescendantOf(HierarchyId parentHierarchyId, HierarchyId childHierarchyId)
    => throw new NotSupportedException();

and later in OnModelCreating for PostgreSQL mapping that to

modelBuilder.HasDbFunction(typeof(ApplicationDb)
    .GetMethod("IsDescendantOf", new[] { typeof(HierarchyId), typeof(HierarchyId) })!,
    builder =>
    {
        builder.HasParameter("parentHierarchyId").HasStoreType("text");
        builder.HasParameter("childHierarchyId").HasStoreType("text");
    });

and trying to execute my custom function with

var hid = HierarchyId.Parse("/");
var data = _applicationDb.Units.Where(x => _applicationDb.IsDescendantOf(x.Node, hid)).Select(a => a.Name).ToList();

so during that last command I got an exception System.InvalidOperationException: Expression '@__8__locals1_hid_1' in the SQL tree does not have a type mapping assigned.

so the question is why .HasStoreType("text") doesn't work for the second parameter, and how can I convert the parameter on runtime ?

[tsardaryanCamenAI]

And in general how can I translate/map any c# type to db supported type for user-defined function ? @roji I've create an issue as you suggested.

[roji]

It seems like you're trying to make the SQL Server HierarchyId type work "as-is" with PostgreSQL - that's unlikely to be a good way forward... PostgreSQL has its own hierarchical type - ltree - which is supported by by the PostgreSQL EF provider (see these translations, I'd strongly suggest changing your application to use that. Porting an application from one database to the other is non-trivial thing, and it generally doesn't work to make the destination database "behave" like the old one.

Specifically in the above, I'm not really sure to what you'd map the IsDescendantOf() function... What SQL would you like to see as the translation for your query, given that you indeed to map hierarchy IDs to strings?

[tsardaryanCamenAI]

here is my IsDescendandOf function in postgresql

CREATE OR REPLACE FUNCTION public."IsDescendantOf"(
	node_hierarchyid text,
	parent_hierarchyid text)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    -- For simplicity, assuming node is descendant if it contains the parent prefix
    RETURN node_hierarchyid LIKE (parent_hierarchyid || '%');
END;
$BODY$;

my idea is to keep HierarchyID in c# code during the complete migration of all databases. and then I'll switch from HierarchyID to Ltree. Till then I would keep those columns as texts and will add those translation functions in the code. Ideally the SQL code should look like something like this, where '/5/' should be passed as 2nd parameter to that function.

select name from units where IsDescendandOf (Node, '/5/')

[roji]

I don't know your exact usage of hierarchyid, but I'd recommend against this sort of plan; depending on which functions you use, you'll likely spend more time on implementing them correctly, and even then you'll end up with a very slow implementation; there's a reason specialized hierarchy support such as hierarchyid/ltree exist, as opposed to people implementing things via string matches.

In any case, the above trouble with the store type sounds like https://github.com/dotnet/efcore/issues/25980.

[weitzhandler]

PostgreSQL has its own hierarchical type - ltree - which is supported by by the PostgreSQL EF provider

Hi @roji I'm not sure if that implies there's similar support for hierarchical data in NpgSql as there is in MSSQL? Is there any docs on how to setup and use a type with hierarchical data for the following structure:

public record TreeItem(int Id, int? ParentId);

[roji]

Have you read the above comments about ltree?