-
Notifications
You must be signed in to change notification settings - Fork 222
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
parameters type mapping to the user-defined function #3174
Comments
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. |
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? |
here is my IsDescendandOf function in postgresql
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.
|
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 dotnet/efcore#25980. |
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
and later in OnModelCreating for PostgreSQL mapping that to
and trying to execute my custom function with
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 ?
The text was updated successfully, but these errors were encountered: