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?
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);