NpgsqlDataSourceBuilder MapEnum doesn't work, Unable to cast object of type 'System.Int32' to type 'System.Enum'

[Kaitbh]

I have upgraded the package to 7.0.4 and using dotnet ef 7.0.10, followed documentation to map CLR type properly. I'm also using dotnet ef dbcontext scaffold to generate the model and dbcontext (DB First). And using partial class to extend the enum property of the entity.

// IServiceCollection Extension
public static void AddMyDbContext(this IServiceCollection serviceCollection, string connectionString)
{
    var dsBuilder = new NpgsqlDataSourceBuilder(connectionString);
        
    // Map enum type (This works)
    // NpgsqlConnection.GlobalTypeMapper.MapEnum<TransactionFlow>();
    
    // This is not working
    dsBuilder.MapEnum<TransactionFlow>();

    var dataSource = dsBuilder.Build();

    serviceCollection.AddDbContext<MyDbContext>(opt =>
    {
        opt.UseNpgsql(dataSource);
        opt.EnableDetailedErrors();
    });
}

// enum TransactionFlow
public enum TransactionFlow
{
    [PgName("IN")]
    IN, 
    [PgName("OUT")]
    OUT
}

// partial class BankTransactionType
public partial class BankTransactionType
{
    [Column("transaction_flow")]
    public TransactionFlow TransactionFlow { get; set; }
}

// partial class DbContext
partial void OnModelCreatingPartial(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BankTransactionType>(entity =>
    {
        entity.Property(e => e.TransactionFlow);
    });
}

// class DbContext (generated code)
public virtual DbSet<BankTransactionType> BankTransactionTypes { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasPostgresEnum("transaction_flow", new[] { "IN", "OUT" });

    OnModelCreatingPartial(modelBuilder);
}

var query = _context.BankTransactionTypes.AsQueryable();
var result = await query.ToListAsync(); // <-- throws error

Error detail:

System.InvalidOperationException: An error occurred while reading a database value for property 'BankTransactionType.TransactionFlow'. The expected type was 'Kaitek.ProjectG.DAL.Enums.TransactionFlow' but the actual value was of type 'System.String'.
 ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Enum'.
   at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.CreateTypeRecord(Type type, INpgsqlNameTranslator nameTranslator)
   at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.<>c.<GetTypeRecord>b__11_0(Type t, INpgsqlNameTranslator translator)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
   at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.GetTypeRecord(Type type)
   at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.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_method182(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   --- End of inner exception stack trace ---
   at lambda_method182(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
   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)
   ...

[FoxTes]

Confirm. Previously, the int32 type was used, now an enum error occurs because of this.

[roji]

Thanks, I'll take a look at this soon.

[FoxTes]

Version 7.0.0: WorkoutType = table.Column<int>(type: "integer", nullable: false), Version 7.0.11 WorkoutType = table.Column<WorkoutType>(type: "workout_type", nullable: false),

public enum WorkoutType

Is this the right behavior?

[pvg8v6g]

i can confirm that this is still an issue. mapping postgres enums does not seem to work properly at the moment

An exception occurred while iterating over the results of a query for context type 'SimpleLtc.Orm.OasisDatabase.OasisContext'.
2024-01-09T17:27:59.519393149Z System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Enum'.
2024-01-09T17:27:59.519395927Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.CreateTypeRecord(Type type, INpgsqlNameTranslator nameTranslator)
2024-01-09T17:27:59.519398073Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.<>c.<GetTypeRecord>b__11_0(Type t, INpgsqlNameTranslator translator)
2024-01-09T17:27:59.519400661Z    at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
2024-01-09T17:27:59.519402939Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.GetTypeRecord(Type type)
2024-01-09T17:27:59.519405025Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
2024-01-09T17:27:59.519407185Z    at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
2024-01-09T17:27:59.519409388Z    at Npgsql.NpgsqlDataReader.GetInt32(Int32 ordinal)
2024-01-09T17:27:59.519411515Z    at lambda_method997(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
2024-01-09T17:27:59.519413604Z    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
2024-01-09T17:27:59.519415447Z System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Enum'.
2024-01-09T17:27:59.519416892Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.CreateTypeRecord(Type type, INpgsqlNameTranslator nameTranslator)
2024-01-09T17:27:59.519418354Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.<>c.<GetTypeRecord>b__11_0(Type t, INpgsqlNameTranslator translator)
2024-01-09T17:27:59.519420011Z    at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
2024-01-09T17:27:59.519432101Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.GetTypeRecord(Type type)
2024-01-09T17:27:59.519433704Z    at Npgsql.Internal.TypeHandlers.UnmappedEnumHandler.ReadCustom[TAny](NpgsqlReadBuffer buf, Int32 len, Boolean async, FieldDescription fieldDescription)
2024-01-09T17:27:59.519435247Z    at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
2024-01-09T17:27:59.519436676Z    at Npgsql.NpgsqlDataReader.GetInt32(Int32 ordinal)
2024-01-09T17:27:59.519438723Z    at lambda_method997(Closure, QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator)
2024-01-09T17:27:59.519440222Z    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

[snebjorn]

Using Npgsql.EntityFrameworkCore.PostgreSQL v8.0.4 I had to do the following to get enums to sort of work

in Program

// Program.cs
var dataSourceBuilder = new NpgsqlDataSourceBuilder(
    builder.Configuration.GetConnectionString("DefaultConnection")
);
dataSourceBuilder.MapEnum<MyEnum>();
var dataSource = dataSourceBuilder.Build();
builder.Services.AddDbContext<MyDbContext>(options => options.UseNpgsql(dataSource));

in DbContext

// MyDbContext.cs
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasPostgresEnum<MyEnum>();
}

With this EF seems to understand how to read an entity using MyEnum. However migrations completely ignore MyEnum.

Some ENUM love would be welcomed :)

[roji]

Note that lots of love was given to enums recently in #3167 (for 9.0); I'll try to also write the docs for this soon (and also release EFCore.PG preview.4). In a nutshell, you can now do MapEnum() at the EF level (instead of at the lower NpgsqlDataSourceBuilder level), and that takes care of everything for you.

Am going to go ahead and close this for now, but if the latest 9.0 versions with the new API still don't work, please let me know and I'll investigate.