Enum with custom "upper-case" names are treated as int for searching with "contains"

[Zwergenpunk]

Hey,

I found problem with the 9.x release.

We have a "PascalCase" naming convention for tables and enums. (yeah probably not good, but we have it)
When i set a cusom name that has no upper case letter it works.

When I search for models with some enums:

List<MyEnum> listOfEnums = [MyEnum.value1];
var result = await context.Set<MyEntity>().AsQueryable().Where(item => listOfEnums.Contains(item.MyEnum)).ToListAsync();

I receive an error:

Unhandled exception. Npgsql.PostgresException (0x80004005): 42883: Operator existiert nicht: doller_test."MyEnum" = integer

POSITION: 89
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Program.<Main>$(String[] args) in C:\Users\MicFiller\Documents\Test\MariaDbTest\PostgresTest\Program.cs:line 51
   at Program.<Main>(String[] args)
  Exception data:
    Severity: FEHLER
    SqlState: 42883
    MessageText: Operator existiert nicht: doller_test."MyEnum" = integer
    Hint: Kein Operator stimmt mit dem angegebenen Namen und den Argumenttypen überein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
    Position: 89
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_oper.c
    Line: 731
    Routine: op_error

The problem is that the generated sql somehow assumes that the parameter is an int:

      Failed executing DbCommand (72ms) [Parameters=[@__listOfEnums_0={ '0' } (DbType = Object)], CommandType='Text', CommandTimeout='30']
      SELECT m."Id", m."MyEnum", m."Name"
      FROM doller_test."MyEntity" AS m
      WHERE m."MyEnum" = ANY (@__listOfEnums_0)

__listOfEnums_0 should be "value1"

Setup to reproduce:

public class MyDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(
            "Host=localhost;Database=postgres;Username=postgres;Password=postgres",
            option => { RegisterEnum<MyEnum>(option); });
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("doller_test");

        modelBuilder.Entity<MyEntity>(_ => { });

        base.OnModelCreating(modelBuilder);
    }

    private static void RegisterEnum<TEnum>(NpgsqlDbContextOptionsBuilder optionsBuilder)
        where TEnum : struct, Enum
    {
        // works
        // optionsBuilder.MapEnum<TEnum>("enum", schemaName: "doller_test");

        // does not work
        optionsBuilder.MapEnum<TEnum>(typeof(TEnum).Name, schemaName: "doller_test");
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public MyEnum MyEnum { get; set; }
}

public enum MyEnum
{
    value1 = 0,
    value2 = 1
}

// Program.cs
var context = new MyDbContext();

await context.Database.MigrateAsync();

var postgresConnection = (NpgsqlConnection)context.Database.GetDbConnection();
if (postgresConnection.State != ConnectionState.Open)
{
    postgresConnection.Open();
}

postgresConnection.ReloadTypes(); 

List<MyEnum> listOfEnums = [MyEnum.value1];
var result = await context.Set<MyEntity>().AsQueryable().Where(item => listOfEnums.Contains(item.MyEnum)).ToListAsync();

[Zwergenpunk]

I tried to fix the enums with custom migrations in the "main" project, where i cannot share code in the public.
Even with all enum names in snake_case the error is still thrown, even if the small example above does not throw an error. =/

[roji]

Confirmed bug; the trigger is an enum with an upper-case name... We've had so much annoying trouble around enum schemas and quoting... Probably introduced in #3167.

Repro

var context = new MyDbContext();

await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

List<MyEnum> listOfEnums = [MyEnum.Foo];
var result = await context.Set<MyEntity>().AsQueryable().Where(item => listOfEnums.Contains(item.MyEnum)).ToListAsync();

public class MyDbContext : DbContext
{
    public DbSet<MyEntity> Entities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(
            "Host=localhost;Username=test;Password=test",
            o => o.MapEnum<MyEnum>("MyEnum"))
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging();
    }
}

public class MyEntity
{
    public int Id { get; set; }
    public MyEnum MyEnum { get; set; }
}

public enum MyEnum { Foo, Bar }

Our of curiosity, why do you have AsQueryable() in your query, after your Set<MyEntity>()?

---

• NpgsqlEnumTypeMapping's store type is a fully delimited+quoted schema+name type name. This has to be this way, since the store type is what gets used in migrations. We also keep the UnquotedStoreType on NpgsqlEnumTypeMapping, since NpgsqlParameter.DataTypeName requires an unquoted type name. This is all a result of EF having a single type name, and not a structured TypeName/TypeSchema.
• When constructing the array store type name from the element's, we take the element's store type and append []. The element store type here is the quoted one ("MyEnum"), so we end up with "MyEnum"[].
• Then, when looking that up in the enum definitions, we don't find it since the definitions contain unquoted names (MyEnum).
• The solution (for now) is probably to fully parse the enum store name before looking for the definitions in the type mapping source. We already (unreliably) do so to break it down into name and schema, but we don't currently strip away quotes, which we should (note that if there's a dot in the enum name/schema, that would trigger a failure).

[Zwergenpunk]

Our of curiosity, why do you have AsQueryable() in your query, after your Set<MyEntity>()?

Good question. Probably played around with something and forgot to remove.

I think I know why my "main" project still fails. The problem is not the upper case, but the fact that qouting is necessary. Our main schema is called default, which needs to be escaped because it is a postgres keyword. 🙈

If you use the "working" example above but replace the schema with default it breaks.

[roji]

The problem is not the upper case, but the fact that qouting is necessary.

Yeah, that's indeed the source of the problem. I've pushed a fix for 9.0.3, which should be out soon.