Npgsql: How to setup enum mapping in Npgsql v8?

Question

I'm upgrading Npgsql from 7.0.11 to 8.0.2/8.0.3. There are a couple of types in PostgreSQL declared like this:

CREATE TYPE my_type AS ENUM (...)

In SQL, we try to insert or read these types like this:

SELECT foo::my_type FROM BAR
SELECT foo FROM BAR
INSERT INTO BAR(foo) VALUES(@Foo::my_type)

First, I started encountering errors like this:

Writing values of 'MyType[]' is not supported for parameters having no NpgsqlDbType or DataTypeName

Then I tried to mitigate it using the following code:

NpgsqlConnection.GlobalTypeMapper.MapEnum<MyType>("public.my_type", new CustomEnumTranslator());

And it seems that writing errors disappeared. However, the reading errors came up:

System.Data.DataException : Error parsing column 5 (foo=MyType[] - Object)
---- System.InvalidCastException : Unable to cast object of type 'MyType[]' to type 'System.String[]'.

Interestingly, the place where the exception occurs, works with the object value method parameter. At runtime it has a value of MyType[2] (array of 2 Enum values) and then it somehow tries to cast into string[].

Any ideas how to solve this?

Answer 1

My bad. (Writing from a different account under the same name: the previous is no longer used in place where I use the current one)

Looks like it was an issue of migrating custom type handlers. These are Dapper handlers.

We had the following code:

public class EnumArrayTypeHandler<TEnum> : SqlMapper.TypeHandler<TEnum[]> 
    where TEnum : struct, Enum
{
    public override void SetValue(IDbDataParameter parameter, TEnum[] value)
        => parameter.Value = value;

    public override TEnum[] Parse(object value)
        => (TEnum[])value;
}

We changed it to:

public class EnumArrayTypeHandler<TEnum> : SqlMapper.TypeHandler<TEnum[]> 
    where TEnum : struct, Enum
{
    // Definitely works on costructs as VALUES(@StringEnumParameter::my_enum_value)
    // Better check whether it works for straight @StringEnumParameter. PostgreSQL mapping of custom types should have the answer.
    public override void SetValue(IDbDataParameter parameter, TEnum[] value)
        => parameter.Value = value.Select(x => x.ToString()).ToArray();

    public override TEnum[] Parse(object value)
    {
        return value switch
        {
            string[] array => array.Select(Enum.Parse<TEnum>).ToArray(),
            TEnum[] enumArray => enumArray,
            _ => throw new InvalidOperationException("Can't parse enum array type column")
        };
    }
}

And all the errors disappeared. These mapping errors popped up when using multiple schemas - not just public.

As for the mapping registration, we used an instance of INpgsqlTypeMapper. The schema registration is as follows:

INpgsqlNameTranslator Translator;
INpgsqlTypeMapper mapper;
Type enumType;
string schema = "not_public";

mapper.MapEnum(enumType, $"{schema}.{Translator.TranslateTypeName(enumType.Name)}", Translator);