Support of mapping custom database types to custom application types

[dpsenner]

Hi,

we are currently evaluating ways to store measurements into a postgres database with entity framework core as the ORM that drives the backend. There is a postgres extension that implements a type that can handle various units (https://github.com/ChristophBerg/postgresql-unit).

Is it currently possible to implement npgsql type mappers and an entity framework plugin to map custom store types to a custom type that represents the same type in the application? Do you know any blockers that prevent this?

[austindrenski]

That's an interesting extension. It reminds me of the F# units of measure language feature.

Right now, I think the approach would be to write two plugins, one for the ADO.NET driver and one for the EF Core provider. Current examples of plugins include NodaTime, Json.NET, NetTopologySuite, and a few others.

Are you interested in submitting a couple of PRs for this?

/cc @roji @YohDeadfall

[dpsenner]

We are still in the phase of evaluating this option. If we decide to do take this road we may contribute the outcome back but that's a story to talk about in the future.

By quickly hacking several plugins and type mappers I got the following exception when reading a row from the database:

Npgsql.PostgresException (0x80004005): 42883: no binary output function available for type unit

Does the underlying npgsql connection provider allow to map custom datatypes? I can remember to have read somewhere that the npgsql connection provider requires that new postgres database types are added manually and that there is currently no way to plug in custom datatype mapping because the new datatype does not map to any of the existing enumeration members of NpgsqlDbType.

[roji]

Npgsql 4.0 introduced a new plugin model that allows you to do exactly this - add support for additional types. We can still add support for extensions inside Npgsql itself (rather than a plugin).

However, your error seems to indicate that the extension does not implement binary transfer for the type(s) it introduces - ouch. PostgreSQL supports two encodings when transferring values - text and binary. Npgsql is a binary-only driver, and can only natively/easily support types which can be encoded in binary.

To make sure what the situation is, can you please execute the following against a database where your extension has been installed:

SELECT oid,typname,typreceive,typoutput FROM pg_type WHERE typname='unit'

If the typreceive column for your extension's type is null, that means there's no binary support. If that's the case I'd let whoever's maintaining the extension that you need it... It shouldn't be too hard to implement for this extension, I think.

[roji]

Note: opened df7cb/postgresql-unit#21 to ask for binary transfer support, which would be the first step for 1st-class Npgsql support.

[roji]

Note that even if full support for this extensions isn't there, EF Core has a proposed feature which would allow server-side conversions (dotnet/efcore#10861). This would allow you to simply cast the values to text server side before bringing them across, which would be an elegant way to map unit to .NET string.

Closing this issue as there's nothing immediate to be done. If the extension author adds binary support and Npgsql adds support at the ADO.NET level, we can reopen to add support at the EF Core level as well.