Models containing enum values throw Can't write CLR type

[jbrezina]

I get the exception while trying to write to DB any data containing enums mapped to DB as ints (C# classes use enums in model classes and DB use INTEGER)
Can't write CLR type ReportServerORM.Model.TransactionCodeState with handler type Int32Handler

https://github.com/purple-technology/EFE-issue

• update the connection string in TestingORM/DAL/TestingContext.cs
• generate DB using the following command in Package Manager Console in VS
  EntityFramework\update-database -ProjectName TestingORM -StartUpProjectName TestingORM -Verbose
• run - the exception is thrown

[jbrezina]

Similar issue in EF Core #489

[xecollons]

Same problem here. EF6.Npgsql 3.2 and Npgsql 4.0.2.

It worked fine in 3.2.7.

[McDoit]

I can second this, after been upgrading to newest ef6 and npgsql
We run the same app on pgsql and mssql, and were relying on the behavior of 3.2.7

We map enums to int2, int4 or int8 depending on usage and type (for example enum flags for up to 64 values in combination)

[roji]

Thanks to everyone for reporting... @rwasef1830 if you have any free time to check this out that would be great. If not I'll try to take a look at this as soon as I can (hopefully this weekend).

We definitely shouldn't have this regression.

[rwasef1830]

I took a quick look at this, the problem seems to be in the Z.EntityFramework.Extensions nuget package which adds the BulkInsert method used in the repro project. When I removed it and changed the code to a standard entity framework "add to context and save changes" code, it worked completely fine. So there appears to be no regression in the EF6 driver itself.

My guess is that the library does some low level manipulation to do its bulk insert behavior and somehow it is not playing well with recent changes in Npgsql but it is very hard to debug the external NuGet package as it is actually a trial of a closed source package and the binaries are obfuscated.

To other people having the same problem, please provide example/repro code.

[CoderDennis]

Here's my repo that reproduces the problem. https://github.com/CoderDennis/NpgsqlIssue105

I've got an entity with a compound key of a Guid and the Enum type. The error appears when executing .Find

I've run the same code against a Sql Server database and it executes without the error.

[rwasef1830]

@roji It seems to be a general issue in Npgsql, not the EF6 driver.

What seems to be happening is that EF6 is passing the enum value as-is in the DbParameter (with NpgsqlDbType of Int32) to the generated DbCommand, and then in Int32Handler.ValidateAndGetLength (the actual implementation is in NpgsqlSimpleTypeHandler<int>) it calls the generated lambda which does a simple type check between the actual value of the DbParameter (the enum) and int32 which fails, so it throws.

I'm not sure what is the proper 'enums as integers' code flow in Npgsql 4 but it would seem the fix is to try to cast the value to the handler type and throwing if it fails instead of just doing a type check in the generated handler lambda, and then having the rest of the handler code operate on the casted value instead of the original.

[roji]

Oh, you're saying that the 3.2 implementation allowed enums to be sent with NpgsqlDbType.Integer, but 4.0 doesn't support this? I'll take a look?

[theroc]

@roji I can confirm, 3.2 seemed to take inbound enum types and perform an appropriate conversion.

The issue seems to creep in when the enum is utilised in the query itself.

I am able to successfully insert utilising the enum but if utilising an IQueryable or join on the enum type then the conversion fails and the CLR type error is thrown.

Adding an explicit (int) conversion around the type alongside converting any incoming value to int resolves the issue.

@roji slightly related - elmah/PgsqlErrorLog@09a7f92#diff-4a67f303004bfa29b7c780afea2b0c79

4.0 appears to cause issues when retrieving uuid columns. From the looks of it all of these are related to the notion of a string-convertible object from the db.

[viniciusverasdossantos]

I'm really having trouble. Has anyone managed to solve it?

[viniciusverasdossantos]

Do you have any predictions for the correction?

[McDoit]

I'm really having trouble. Has anyone managed to solve it?

Just revert back to 3.2.7, unless there is a specific feature from 4 that you really need

[risulo]

Hi, any update on that? We use some third-party nuget which works as expected and we like its functionality. But this nuget uses calls like this many times: token = await context.Tokens.FindAsync(key, tokenType); where tokenType is enumerator
When we use this nuget with latest Npgsql version (4.0.5) we always get error described in this bug. We had to switch to Npgsql 3.2.7.
Is there any plan to solve this bug?

[haharoi]

Also discovered this bug after upgrading from 3.2.7. Hoped to get improved performance (with Postgres 11.2) but had to revert Npgsql update.
Is there any news on this topic?

[rwasef1830]

I've been preoccupied lately to look at this, last time I checked it was a problem in Npgsql itself (something that changed there), I'll do a deep dive once again and see if there's a way to solve it when I get some time.

[McDoit]

@roji Did you manage to take a look at this with "regression" in Npgsql?

[roji]

@rwasef1830 re-reading this again... Are we simply missing the option for the int handler (and short and long) to accept an enum? If so, that shouldn't be too hard to do. However, this would depend on EF6 setting DbType.Integer (or NpgsqlDbType.Int) - I don't know enough about it.

Can you please confirm?

[rwasef1830]

@roji Yes, the reason for the exception is that the method generated by GenerateNonGenericValidationMethod in case of the numeric handlers is doing an exact type check which fails for enums.

This part is what causes the exception: https://github.com/npgsql/npgsql/blob/f52e1a5d9c189392f1757d0845a992d2bef3d11e/src/Npgsql/TypeHandling/NpgsqlTypeHandler%60.cs#L222

The solution would be to check if the type is an enum and if true to get its underlying type and then continue the check as normal.

[roji]

Opened npgsql/npgsql#2435 to track in Npgsql.

[rwasef1830]

It is possible to fix this in the EF6 driver without modifying Npgsql. I will implement a workaround.

[rwasef1830]

@roji This is fixed, you may want to roll out a new release.

@haharoi @jbrezina @risulo @viniciusverasdossantos @CoderDennis
Until a new release is made, you can add the following EF6 interceptor to your DbContext to workaround the issue:

            class EnumToIntegerInterceptor : DbCommandInterceptor
            {
                public override void NonQueryExecuting(
                    DbCommand command,
                    DbCommandInterceptionContext<int> interceptionContext)
                {
                    ConvertAllEnumsToIntegers(command.Parameters);
                }

                public override void ReaderExecuting(
                    DbCommand command,
                    DbCommandInterceptionContext<DbDataReader> interceptionContext)
                {
                    ConvertAllEnumsToIntegers(command.Parameters);
                }

                public override void ScalarExecuting(
                    DbCommand command,
                    DbCommandInterceptionContext<object> interceptionContext)
                {
                    ConvertAllEnumsToIntegers(command.Parameters);
                }

                public override void ReaderExecuted(
                    DbCommand command,
                    DbCommandInterceptionContext<DbDataReader> interceptionContext)
                {
                }

                public override void ScalarExecuted(
                    DbCommand command,
                    DbCommandInterceptionContext<object> interceptionContext)
                {
                }

                static void ConvertAllEnumsToIntegers(DbParameterCollection parameters)
                {
                    if (parameters == null) throw new ArgumentNullException(nameof(parameters));

                    foreach (DbParameter parameter in parameters)
                    {
                        if (parameter.Value == null)
                        {
                            return;
                        }

                        var parameterValueObjectType = parameter.Value.GetType();

                        if (!parameterValueObjectType.IsEnum)
                        {
                            continue;
                        }

                        var underlyingType = Enum.GetUnderlyingType(parameterValueObjectType);
                        parameter.Value = Convert.ChangeType(parameter.Value, underlyingType);
                    }
                }
            }

[haharoi]

Thanks a lot!

[usmanniqbal]

@rwasef1830 @roji

Any tentative date of the release with the fix?

[roji]

Version 3.2.1 of the provider has just been released with this fix.