Data source enum mapping doesn't work

[pinkfloydx33]

I don't know if this is an issue here or in Npgsql.

I am using Npgsql.EntityFrameworkCore.PostgreSQL version 7.0.0 on net7.0. I am trying to use the new DbDataSource overload of UseNpgsql along with a mapped Postgres Enum. The following:

var dbDataSource = new NpgsqlDataSourceBuilder(Configuration["Postgres:ConnectionString"]);
dbDataSource.MapEnum<AuthRoleType>();
services.AddDbContext<AdministrationContext>(s => s.UseNpgsql(dbDataSource.Build()));

and then this query:

// _database == from DI
var result = await _database.AuthSystemRoles.Where(c => c.Type == AuthRoleType.Admin).ToListAsync(token);

Throws the following exception:

[16:13:13 ERR] An exception occurred while iterating over the results of a query for context type 'Artemis.Administration.Database.AdministrationContext'.
Npgsql.PostgresException (0x80004005): 42883: operator does not exist: auth_role_type = integer

POSITION: 167
at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|222_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, 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.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
Exception data:
Severity: ERROR
SqlState: 42883
MessageText: operator does not exist: auth_role_type = integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 167
File: parse_oper.c
Line: 731
Routine: op_error
Npgsql.PostgresException (0x80004005): 42883: operator does not exist: auth_role_type = integer

POSITION: 167
at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|222_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, 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.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
Exception data:
Severity: ERROR
SqlState: 42883
MessageText: operator does not exist: auth_role_type = integer
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 167
File: parse_oper.c
Line: 731
Routine: op_error
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
[16:13:13 INF] Executed action Artemis.Administration.Web.API.Controllers.RolesController.List (Artemis.Administration.Web.API) in 8580.0808ms
[16:13:13 INF] Executed endpoint 'Artemis.Administration.Web.API.Controllers.RolesController.List (Artemis.Administration.Web.API)'
Exception thrown: 'Npgsql.PostgresException' in System.Private.CoreLib.dll
[16:13:13 ERR] An unhandled exception has occurred while executing the request.
Npgsql.PostgresException (0x80004005): 42883: operator does not exist: auth_role_type = integer

POSITION: 167
at Npgsql.Internal.NpgsqlConnector.g__ReadMessageLong|222_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, 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.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at Artemis.Administration.Web.API.Controllers.RolesController.List(RoleSearchRequest request, CancellationToken token) in C:\Development\Administration\src\Artemis.Administration.Web.API\Controllers\RolesController.cs:line 75
at lambda_method9(Closure, Object)
[ASP stack elided]

I also get a different error when trying to project to a dictionary:

var result = await _database.AuthSystemRoles.ToDictionaryAsync(c => c.AuthId, token);

[16:11:21 ERR] An unhandled exception has occurred while executing the request.
System.InvalidOperationException: An error occurred while reading a database value for property 'AuthSystemRole.Type'. The expected type was 'Artemis.Administration.Database.Models.AuthRoleType' but the actual value was of type 'Artemis.Administration.Database.Models.AuthRoleType'.
---> System.InvalidCastException: Can't cast database type public.auth_role_type to Int32
at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
at Npgsql.NpgsqlDataReader.GetInt32(Int32 ordinal)
at lambda_method22(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
--- End of inner exception stack trace ---
at lambda_method22(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToDictionaryAsync[TSource,TKey,TElement](IQueryable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer1 comparer, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToDictionaryAsync[TSource,TKey,TElement](IQueryable1 source, Func2 keySelector, Func2 elementSelector, IEqualityComparer`1 comparer, CancellationToken cancellationToken)
at Artemis.Administration.Web.API.Controllers.RolesController.List(RoleSearchRequest request, CancellationToken token) in C:\Development\Administration\src\Artemis.Administration.Web.API\Controllers\RolesController.cs:line 74
at lambda_method9(Closure, Object)
[ASP stack elided]

I found this line interesting:

The expected type was 'Artemis.Administration.Database.Models.AuthRoleType' but the actual value was of type 'Artemis.Administration.Database.Models.AuthRoleType'.

However if I continue to use the Global Type mapper, everything works fine:

public class AdministrationContext : DbContext
{
    static AdministrationContext()
         => NpgsqlConnection.GlobalTypeMapper.MapEnum<AuthRoleType>();

    public AdministrationContext(DbContextOptions<AdministrationContext> options)
        : base(options)
    {
    }
    public virtual DbSet<AuthSystemRole> AuthSystemRoles => Set<AuthSystemRole>();
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresExtension("uuid-ossp"); 
        modelBuilder.HasPostgresExtension("citext"); 

        modelBuilder.HasPostgresEnum<AuthRoleType>();

        modelBuilder.Entity<AuthSystemRole>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id)
                .HasDefaultValueSql("uuid_generate_v4()")
                .HasColumnName("id");
            entity.Property(e => e.Type)
                .HasDefaultValueSql("'normal'")
                .HasColumnName("type")
               // .HasColumnType("auth_role_type") // needed without global mapper 
                .IsRequired();
            entity.Property(e => e.AuthRoleName)
                .HasColumnName("auth_role_name")
                .HasColumnType("citext");
            entity.Property(e => e.AuthId)
                .HasColumnName("auth_id");
        });
    }
}

public class AuthSystemRole
{
    public Guid Id { get; set; }
    public string AuthRoleName { get; set; } = null!;
    public AuthRoleType Type { get; set; }
    public string AuthId { get; set; } = null!;
}
public enum AuthRoleType
{
    Normal = 0,
    Admin = 1, 
    Manager = 2
}

If it matters I am running on Postgres 11.

Edit: added explicit column names (my actual context uses a plugin for snake case naming which I've removed in my local repro as well)

[pinkfloydx33]

Something made me try and run a migration. Nothing has changed up to this point. Running Add-Migration with the global type mapper configured produces an empty migration. However running it without the global type mapper produces this:

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy

public partial class Test : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "type",
            table: "auth_system_roles",
            type: "integer",
            nullable: false,
            defaultValueSql: "'normal'",
            oldClrType: typeof(int),
            oldType: "auth_role_type",
            oldDefaultValueSql: "'normal'");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<int>(
            name: "type",
            table: "auth_system_roles",
            type: "auth_role_type",
            nullable: false,
            defaultValueSql: "'normal'",
            oldClrType: typeof(int),
            oldType: "integer",
            oldDefaultValueSql: "'normal'");
    }
}

Notice that the type is being changed.

I realized that we are using a design-time factory and that was still using a connection string. I converted that over to Data source builder and mapped the Enum there as well (should this be needed in this case?). This didn't change anything and I still get the same migration.

If I explicitly set the column type auth_role_type on the property I don't get the above migration any more (I get the empty one) but this doesn't change anything else--I still get the exceptions.

        entity.Property(e => e.Type)
                .HasDefaultValueSql("'normal'")
                .HasColumnName("type")
                .HasColumnType("auth_role_type") // add this, no more migration
                .IsRequired();

[ This is probably a red herring but shouldn't the column type be inferred properly without anything special as before? We use a library for snake-casing-ifying tables/columns/types etc. and I'd prefer not to be specifying the type name directly ]

[pinkfloydx33]

Something else I discovered... I created a new blank database trying to determine if there was something corrupt about my current one (there isn't). But in the process I started with some blank data sets. If I run the queries above against the tables without any data in them they return just fine (but empty, of course).

If I try adding a record to the database, I get weird behavior. For example this will throw an exception--but it will add the value to the underlying database:

_database.AuthSystemRoles.Add(new AuthSystemRole { AuthRoleName = "x", Type = AuthRoleType.Normal });
await _database.SaveChangesAsync();

And the thrown exception:

An exception occurred in the database while saving changes for context type 'Artemis.Administration.Database.TestContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
---> System.InvalidCastException: Can't cast database type public.auth_role_type to Int32
at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
at Npgsql.NpgsqlDataReader.GetInt32(Int32 ordinal)
at lambda_method38(Closure, DbDataReader, Int32)
at Microsoft.EntityFrameworkCore.RelationalPropertyExtensions.GetReaderFieldValue(IProperty property, RelationalDataReader relationalReader, Int32 ordinal, Boolean detailedErrorsEnabled)
at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommand.PropagateResults(RelationalDataReader relationalReader)
at Npgsql.EntityFrameworkCore.PostgreSQL.Update.Internal.NpgsqlModificationCommandBatch.Consume(RelationalDataReader reader, Boolean async, CancellationToken cancellationToken)
--- End of inner exception stack trace --
[Elided]

[pinkfloydx33]

Also note that the exception messages from the OP are based on my actual context class which has a lot more to it (more types, snake case naming plugin, etc). I have reproduced this with a new context, exactly as it appears in the OP (which I've modified slightly to be standalone). The exceptions are mostly the same, the overall problem persisting. Everything works fine if I use the global type mapper (along with the data source).

[meck-gd]

I'm having the same problem. EF7 seems to ignore enum mappings created in NpgsqlDataSourceBuilder. When creating an initial migration, all enum columns are created as type int rather than the actual enum type. When the driver then attempts to insert enum values at runtime, exceptions occur.

Using NpgsqlConnection.GlobalTypeMapper.MapEnum<>(); in the static DbContext constructor fixes this, but throws a deprecation warning.

[HHaoWang]

I have the same problem. When using dbDataSource to map enum the migration would use int as the field's default type. While when using NpgsqlConnection.GlobalTypeMapper to map enum it would use the specific type but it is obsolete. By the way, can we add more description in the ef core part of the document? I was really confused about how to use dataSource to map enum in a asp.net project until I found examples in the issue #2603. And ef core part of the document does not mention that NpgsqlConnection.GlobalTypeMapper is obsolete.