efcore.pg: Where queries with casted enum value throw Can't write CLR type

If i understand what your are talking about, my 2 cents: this does not involve Postgres enums in any way. The DB column is a simple int column. The enum exsists only in C#, that is why it is casted to int when passed into expression. There are no enums in the DB.

@austindrenski done 😃

It looks like your project is using EF6, not EF Core.

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net462" />
  <package id="Npgsql" version="4.0.2" targetFramework="net461" />
  <package id="System.Runtime.CompilerServices.Unsafe" version="4.5.1" targetFramework="net461" />
  <package id="System.Threading.Tasks.Extensions" version="4.5.1" targetFramework="net461" />
  <package id="System.ValueTuple" version="4.5.0" targetFramework="net461" />
  <package id="Z.EntityFramework.Extensions" version="3.15.5" targetFramework="net461" />
</packages>

Would you mind opening this issue with the repo for the Npgsql EF6 provider?

I looked into this, and the Microsoft.Data.Sqlite ADO.NET provider accepts CLR enums and unwraps them into ints (I assume the SQL Server provider does the same). So this has nothing to do with EF Core, which simply hands off the enum to the underlying ADO.NET provider; Npgsql just happens not to support this. Unless I’m mistaken that means the cast to int is actually sent to the server (haven’t inspected the SQL), and ends up doing nothing because the value is already an int.

We could think about making Npgsql (ADO.NET) do the same (as @austindrenski proposed above). This would mean the following behavior:

1. If you map the enum (recommended), the CLR enum type always gets sent as the PostgreSQL enum.
2. If you don’t map the enum but specify DataTypeName, Npgsql still sends the PostgreSQL enum.
3. If you don’t map the enum and don’t specify DataTypeName, Npgsql sends an integer (or smallint or bigint). This would be the new behavior.

To be honest, this seems pretty confusing, and doesn’t fit with the strict, strongly-typed behavior that both PostgreSQL and Npgsql implement. We’re currently asking users to perform the cast (unwrapping the enum) outside the driver, rather than doing it for them inside the driver, just like we avoid doing other types of casts and conversions. It would also confuse first-time users who want to use PostgreSQL enums but getting integers (or integer-related errors) because they haven’t properly mapped or set DataTypeName).

I understand that this gets a bit more complicated with EF Core specifically, because unwrapping outside the driver is a bit less nice. But this still seems reasonable to me, and is unfortunately a small database portability issue.

I’m going to go ahead and close this for now, but am of course open to hearing opinions to the contrary.

@austindrenski Yes, it’s convention based foreign keys.

@roji Yes, it fixes the issue.

So it’s either changing to enums everywhere in the model or removing enums from queries.

I understand that this works as intended, but personally I can’t think of a reason when passing this cast operation to Postgres would make sense. Also, casting the enum directly (without variable): (int)VehicleTypes.Suv does not throw. This is confusing in my opinion.

@onimoger Is there any reason that you can’t model your type_id integer column with the unmapped CLR enum?

I guess it was because db was modeled before the enum was introduced to make C# code clearer when “hardcoded” type id’s became required for business logic.

I will have to try it out then.