Where queries with casted enum value throw Can't write CLR type

[onimoger]

After upgrading to version 2.1 started getting errors with such queries:
var t = VehicleTypes.Truck;
context.Vehicles.Where(x => x.TypeId == (int)t)
throwing exception:

System.InvalidCastException: Can't write CLR type VehicleTypes with handler type Int32Handler

If code is changed to:
int t = (int)VehicleTypes.Truck;
context.Vehicles.Where(x => x.TypeId == t)
it works.

I guess it's because of new strict parameter type handling, but is this really the place for it trigger error? The enum value is supplied with a cast to correct type.
Is this intended? It would require a lot of refactoring in our project.

[roji]

This should definitely work, I'll try to take a look in the coming days.

[onimoger]

My hasty example was wrong. Error only occurs when enum in Where condition comes from a variable.

I have created a console app with exact reproduction of this problem:
https://github.com/onimoger/npgsql4repro

Hopefully this helps

[austindrenski]

@onimoger Thanks for providing that repro, I was able to replicate this issue in our test suite.

@roji I opened #490 to add this to the test suite (currently fails). If you want to tackle this one, I can set the test to skip for the merge. Otherwise, I can take a look this evening.

[roji]

Nope, go ahead and take this if you want! The idea is to release 2.1.1 (and Npgsql 4.0.1) this weekend (probably Sunday). If you get stuck feel free to ping me.

[austindrenski]

I took a look and it gets interesting:

The type cast is recorded in the expression tree as expected, so it does go through the normal visitor process. But since PostgreSQL doesn't support casting enums to numeric values, the normal type cast handling generates SQL that PostgreSQL will reject.

I'm not sure if there is a good option from the EF Core side. I can't seem to find access to parameter values before NpgsqlQuerySqlGenerator, and by that point, changing the value or type of a hoisted variable sounds troublesome (e.g. is the original value used elsewhere? should we create a new parameter? embed a literal?).

If the parameter values were available in NpgsqlSqlTranslatingExpressionVisitor, then perhaps we could cast to the underlying type and pass back a ConstantExpression. But so far I haven't found a way to access them from inside this visitor.

Another idea that comes to mind would be to make changes at the Npgsql level and relax NpgsqlSimpleTypeHandler<T> to support enums when their underlying type matches the generic parameter. This sounds like it could be useful, but I'm not sure how it would impact the larger type handler/resolution system.

@roji Any thoughts?

[roji]

@austindrenski can you please confirm that this error only occurs when the enum is mapped to a PostgreSQL enum? In other words, that NpgsqlConnection.GlobalTypeMapper.MapEnum() is being called? Unmapped enums are automatically mapped by EF Core to database int columns, and in this case the above code should be working correctly - it would be good if you could confirm.

If this is the situation, then I think the error is expected behavior. On disk, PostgreSQL stores enums values as an int, but the wire representation of enums (when sending/receiving values) is actually the string value - this is why we are able to handle unmapped enums as strings. This means that there is no way for us to send an integer value to PostgreSQL for storage as an enum: PostgreSQL simply does not support that. That rules out changing the Npgsql ADO.NET handler for enums to support integers.

It's also unclear for me why one would map a CLR enum to a PostgreSQL enum, and then want to interact with it as an integer. The whole point of mapping CLR enums to PostgreSQL enums is that you don't deal with the underlying integral value.

More generally, when you put code inside an IQueryable, that means that in principle you're asking for the expression to be evaluated at the server. So in the code fragment context.Vehicles.Where(x => x.TypeId == (int)t), assuming t is an enum type, you're literally asking PostgreSQL to cast an enum to an int, which simply isn't supported. So it actually makes sense for me that this throws. The exception itself is a little strange/unexpected: I would have preferred EF Core to translate the cast to a server-side cast, and for that to generate a PostgreSQL error. I suspect this has to do with some specific enum handling inside EF Core. But that isn't important enough IMHO.

A final note on complex fragment translations... it's probably theoretically possible to perform complex expression tree fragment translation, detect the case of a mapped enum being cast to an integral type, resolve that back to the enum value for that integral type and send that. However, as a matter of principle I prefer we avoid doing that unless there's real value in it - it makes the provider much more complex, usually involves interacting with internal EF Core APIs which may change, etc. etc. This is also part of the reason why I'm a bit reluctant to continue in the direction of #444/#431... It's definitely not a an absolute no or anything, just that we need to have a clear demonstration of value (and #431 has the null behavior discrepancy issue which seems to stand in the way).

Let me know what you think.

[onimoger]

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]

@roji wrote:

@austindrenski can you please confirm that this error only occurs when the enum is mapped to a PostgreSQL enum? In other words, that NpgsqlConnection.GlobalTypeMapper.MapEnum() is being called? Unmapped enums are automatically mapped by EF Core to database int columns, and in this case the above code should be working correctly - it would be good if you could confirm.

The test was using mapped enums, so I updated #490 to use an unmapped enum for Where_with_parameter_downcast(). Unfortunately, it still draws the exception:

System.InvalidCastException : 
  Can't write CLR type Npgsql.EntityFrameworkCore.PostgreSQL.Query.EnumQueryTest+UnmappedEnum
  with handler type Int32Handler

I'm working on a change in NpgsqlSqlTranslatingExpressionVisitor to force the SQL generation for enums that are down-cast to their underlying system type:

/// <inheritdoc />
protected override Expression VisitUnary(UnaryExpression expression)
{
    if (expression.NodeType is ExpressionType.Convert &&
        expression.Operand.Type.IsEnum &&
        Enum.GetUnderlyingType(expression.Operand.Type) == expression.Type &&
        Visit(expression.Operand) is Expression e)
        return new ExplicitCastExpression(e, expression.Type);

    return base.VisitUnary(expression);
}

This works for unmapped enums, generating the following SQL which is accepted by PostgreSQL. In this case, the cast expression isn't actually needed, since we sent the parameter as Int32. But it seems to disrupt a later stage, avoiding the InvalidCastException. (Basically, addressing the symptoms, not the cause...)

-- The parameter type is right, so handlers take it
(DbType = Int32)

-- The SQL syntax is right, though redundant
WHERE e."EnumValue" = CAST(@__sad_0 AS integer)

Unfortunately, that causes mapped enums to draw the following exception when used in binary comparisons:

Npgsql.PostgresException : 42846: cannot cast type mapped_enum to integer

I was surprised by this, since the test cases do not cast to int....at least in the source code:

[Fact]
public void Where_with_parameter()
{
    using (var ctx = CreateContext())
    {
        var sad = MappedEnum.Sad;
        var x = ctx.SomeEntities.Single(e => e.MappedEnum == sad);
        Assert.Equal(MappedEnum.Sad, x.MappedEnum);
        AssertContainsInSql("(DbType = Object)");
        AssertContainsInSql("WHERE e.\"MappedEnum\" = @__sad_0");
    }
}

However, in the expression tree, we can see that enums are cast to their underlying system types before comparison:

(Convert([e].MappedEnum, Int32) == Convert(__sad_0, Int32))

I think a proper solution that handles @onimoger example, and doesn't break mapped enums will need to address this from NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(...), rather than in NpgsqlSqlTranslatingExpressionVisitor.VisitUnary(...) where we can't tell if the cast is user-specified or system-specified.

---

@roji wrote:

If this is the situation, then I think the error is expected behavior. On disk, PostgreSQL stores enums values as an int, but the wire representation of enums (when sending/receiving values) is actually the string value - this is why we are able to handle unmapped enums as strings. This means that there is no way for us to send an integer value to PostgreSQL for storage as an enum: PostgreSQL simply does not support that. That rules out changing the Npgsql ADO.NET handler for enums to support integers.

I agree with all of this, but I think we might be talking about different issues.

I didn't mean to suggest that we modify the enum handlers, but rather that the integral type handlers be relaxed to support an enum whose underlying system type is supported. (Though, this could be untenable from an implementation or maintenance perspective.)

But it does sound reasonable from a type system perspective. While down-casting enums is never supported in PostgreSQL, it is—setting aside opinions of whether one ought to do so—a common-enough operation in C#.

@roji wrote:

It's also unclear for me why one would map a CLR enum to a PostgreSQL enum, and then want to interact with it as an integer. The whole point of mapping CLR enums to PostgreSQL enums is that you don't deal with the underlying integral value.

I could imagine a few scenario where the CLR enum is mapped to a database enum, but the CLR enum has explicit values assigned that need to occasionally be accessed (cast on the client) and sent as parameter of a query. For example, the underlying values could:

• map to an external data store,
• provide compatibility to a legacy database, or
• be used during migration from an RDBMS that didn't support enums to PostgreSQL.

None of this is to say that Npgsql needs to support this behavior, just that there could be cases in the wild where this behavior is both intentional and useful.

@onimoger wrote:

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.

Thank you for additional clarification. We are indeed talking about a couple of different use cases:

1. CLR enums mapped to PostgreSQL enums for use as enums
2. CLR enums not mapped to PostgreSQL for use as integral types
3. CLR enums mapped to PostgreSQL for use as both enums and integral types (separate from the mapped enum in the database)

It sounds like you are in the second case. We expect both the first and the second cases to work, with the third case being nice to have.

The root of the exception you're seeing seems to be related to the way in which the CLR handles binary comparisons on enums (i.e. casting to the underlying system type). I think the minimal outcome of this issue and #490 should be to ensure that unmapped enums are supported in binary comparisons. (Currently, it seems that this is not the case.)

---

@roji wrote:

More generally, when you put code inside an IQueryable, that means that in principle you're asking for the expression to be evaluated at the server. So in the code fragment context.Vehicles.Where(x => x.TypeId == (int)t), assuming t is an enum type, you're literally asking PostgreSQL to cast an enum to an int, which simply isn't supported. So it actually makes sense for me that this throws. The exception itself is a little strange/unexpected: I would have preferred EF Core to translate the cast to a server-side cast, and for that to generate a PostgreSQL error. I suspect this has to do with some specific enum handling inside EF Core. But that isn't important enough IMHO.

I agree that it would be desirable to force this translation and draw the error from PostgreSQL. This would be the best case result for #490 (once the casting issues are dealt with).

---

@roji wrote:

A final note on complex fragment translations... it's probably theoretically possible to perform complex expression tree fragment translation, detect the case of a mapped enum being cast to an integral type, resolve that back to the enum value for that integral type and send that. However, as a matter of principle I prefer we avoid doing that unless there's real value in it - it makes the provider much more complex, usually involves interacting with internal EF Core APIs which may change, etc. etc. This is also part of the reason why I'm a bit reluctant to continue in the direction of #444/#431... It's definitely not a an absolute no or anything, just that we need to have a clear demonstration of value (and #431 has the null behavior discrepancy issue which seems to stand in the way).

I think we're on the same page with this. When I have some time to circle back to #431, I'll try to address the concerns raised in #460 by reducing/eliminating the fragment translations. I still think they're a useful approach, but as you say, they do make things more complex. Perhaps they'll provide a better value proposition in future versions of EF Core.

[austindrenski]

@austindrenski wrote:

I think a proper solution that handles @onimoger example, and doesn't break mapped enums will need to address this from NpgsqlSqlTranslatingExpressionVisitor.VisitBinary(...), rather than in NpgsqlSqlTranslatingExpressionVisitor.VisitUnary(...) where we can't tell if the cast is user-specified or system-specified.

#490 now passes both the previous enum tests and the repro for this issue. It does still need some additional tests to make sure the translation is strictly limited to unmapped enums cast to their exact underlying system type and used in an equality comparison and nothing else.

@roji wrote:

A final note on complex fragment translations... it's probably theoretically possible to perform complex expression tree fragment translation, detect the case of a mapped enum being cast to an integral type, resolve that back to the enum value for that integral type and send that. However, as a matter of principle I prefer we avoid doing that unless there's real value in it

I've tried to keep this in mind while working on #490, keeping the translation as narrow as possible to limit the complexity (and specifically limiting it to unmapped enums).

That said, this was a fair bit more complicated than I anticipated, so I'm not sure how I would rate the value/risk of introducing this feature.

If you think it's worthwhile to support this type of casting on unmapped enums, then #490 is close to what we need. But if not, we could always shelve it and reconsider it at a later release. I'm good either way.

[roji]

Apologies, I was under the impression the issues where with mapped enums. It's definitely more serious/interesting if this happens to unmapped enums, which EF Core maps to int columns. I agree we need to make sure that binary comparison of unmapped ints works.

But before going into fragment translation, I have a much simpler question - how is this working on other providers (e.g. SQL Server)? After all, they're handling unmapped enums as ints, since they don't support anything else. So either SQL Server also experiences this issue (in which case it's better raised to the EF Core team for handling in the core), or it doesn't and there's something we're doing in Npgsql to trigger this bug (in which case we should investigate that and find the root cause rather than work around it via fragment translation). Any chance you can look into this @austindrenski? Note that you don't need a full-fledged SQL Server installation, you can either use LocalDB (which is an SQL Server-compatible lightweight local db that comes pre-installed) or possibly even SQLite.

Some responses to the comments above:

I didn't mean to suggest that we modify the enum handlers, but rather that the integral type handlers be relaxed to support an enum whose underlying system type is supported. (Though, this could be untenable from an implementation or maintenance perspective.)

To me this doesn't make any more sense than modifying the enum handlers. Once again, PostgreSQL does not transferring enums over the wire as ints (even if it does store them internally as ints on disk). So as far as I can tell the Npgsql ADO.NET is fine and doesn't need any modification, any issue here is at the EF Core layer.

It's also unclear for me why one would map a CLR enum to a PostgreSQL enum, and then want to interact with it as an integer. The whole point of mapping CLR enums to PostgreSQL enums is that you don't deal with the underlying integral value.

I could imagine a few scenario where the CLR enum is mapped to a database enum, but the CLR enum has explicit values assigned that need to occasionally be accessed (cast on the client) and sent as parameter of a query. For example, the underlying values could:

map to an external data store,
provide compatibility to a legacy database, or
be used during migration from an RDBMS that didn't support enums to PostgreSQL.

I'm still not seeing why the enum would need to be sent as an integer to PostgreSQL (as opposed to some other EF Core provider or whatever). But in any case we don't have a request to support this, so as you suggest above we can drop it at least for now.

[onimoger]

I'm still not seeing why the enum would need to be sent as an integer to PostgreSQL

Because it's CLR enum, and in DB it's an int column referencing another table. Granted, this practice comes from using EF with Sql Server. Postgres enums are not used because of decision to keep with generic sql as much as possible, in case we need to change DB. If this would not have worked from the start, i would say ok, we can work around it, but it works fine with Npgsql 3.2.7

We have a system running with lots of such code, and this is a blocker for us, preventing upgrade to .net core 2.1
Refactoring is complicated, since, unfortunately, we do not have 100% test coverage, and this error only manifests during runtime.