Npgsql 3.0.0 Fails to Parse Enumerated Types

c# dapper npgsql postgresql

Question

I have enumerated types in Postgres, defined as follows:

CREATE TYPE "SomeEnumType" AS ENUM (
    'Val1',
    'Val2'
);

I have the equivalent enum defined in C#:

public enum SomeEnumType {
    Val1,
    Val2
}

When I updated to Npgsql v3.0.0, deserializing a class with a property of this type fails. For example, I have:

CREATE TABLE Foo (
    Field1 "SomeEnumType" NOT NULL
);

And in code:

public class Foo {
    public SomeEnumType Field1 { get; set; }
}

When I go to deserialize my class, I get the error

Error parsing column 0 (Field1=The field Field1 has a type currently unknown to Npgsql (OID 6965926). You can retrieve it as a string by marking it as unknown, please see the FAQ.)

I tried to add a type handler to Dapper, but that didn't resolve the exception. I found a related question and answer which points to a FAQ page on the Npgsql site. However, the only answers on that page appear to be:

1) Change the query to explicitly cast unknown types to built-in types
2) Change all Npgsql queries to forgo the use of binary encoding

The first solution dramatically complicates my queries, as the real tables have many columns. For example, using this solution changes all my queries from:

SELECT * FROM Foo

To:

SELECT Column1, Column2, Column3, Field1::TEXT, Column4 FROM Foo

This is clearly an unacceptable solution, since any number of changes (reordering columns, adding columns, dropping columns, changing column types, etc.) may require changing the query. The second solution is more maintainable, but requires more data to be sent to/from the server, which will result in a performance impact. Is there any other (read as: more maintainable) way to get Npgsql v3.0.0 to parse enumerated types?

Accepted Answer

The solution is to register each enum prior to creating any connections. In my case, this line was added to the static constructor of my data access layer class:

NpgsqlConnection.RegisterEnumGlobally<SomeEnumType>(
    typeof(SomeEnumType).Name);

If you are using case-insensitive naming in Postgres (e.g., didn't quote the Postgres data type), then you don't need to pass any parameters to the RegisterEnumGlobally, since the default value is the lowercase name of the C# type.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why