Troubleshooting enums with efcore.pg

Question

Are there any best practices in troubleshooting enums when using with Npgsql.EntityFrameworkCore.PostgreSQL provider? It's just that I don't know where to look anymore. Here is how I register them:

public class Client
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public SexType SexType { get; set; }
}

public enum SexType
{
    Male,
    Female,
}

public class MedServiceDbContext : DbContext
{
    public const string DatabaseSchema = "public";

    public DbSet<Client> Clients { get; protected set; }

    static MedServiceDbContext()
    {
        NpgsqlConnection.GlobalTypeMapper.MapEnum<SexType>($"{DatabaseSchema}.{NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator.TranslateTypeName(nameof(SexType))}", NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator);

        //NpgsqlConnection.GlobalTypeMapper.MapEnum<SexType>();
    }

    private static void MapTypes(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasPostgresEnum<SexType>(DatabaseSchema, NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator.TranslateTypeName(nameof(SexType)), NpgsqlConnection.GlobalTypeMapper.DefaultNameTranslator);
        ;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        MapTypes(modelBuilder);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) =>
        optionsBuilder
            .UseNpgsql(@"Host=localhost;Database=TestEnum;Username=postgres;Password=123")
            .LogTo(Console.WriteLine, LogLevel.Trace)
            .EnableSensitiveDataLogging()
        ;

    public MedServiceDbContext()
    {
    }
}

And this is how a migration looks like:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterDatabase()
        .Annotation("Npgsql:Enum:public.sex_type", "male,female");

    migrationBuilder.CreateTable(
        name: "Clients",
        columns: table => new
        {
            Id = table.Column<int>(type: "integer", nullable: false)
                .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
            Name = table.Column<string>(type: "text", nullable: false),
            SexType = table.Column<SexType>(type: "public.sex_type", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Clients", x => x.Id);
        });
}

And when used in a clean repro project it works flawlessly. It also works in a Test. But the same code (copy pasted) doesn't work in the main project giving me:

System.InvalidOperationException: An error occurred while reading a database value for property 'Client.SexType'. The expected type was 'MedService.API.Database.Entities.Enums.SexType' but the actual value was of type 'MedService.API.Database.Entities.Enums.SexType'.
 ---> System.InvalidCastException: Can't cast database type public.sex_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_method256(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   --- End of inner exception stack trace ---
   at lambda_method256(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()

I've checked every relevant piece of code but the only difference is how the database context is created. The main project uses DI the same way like a Test project whereas repro creates a context directly. So logically if main project and a Test project work the same they both should behave the same, but they are not.

I've checked every generated SQL scripts, LINQ expressions, etc. Everything seems to be same, but I keep getting this exception.

Where else should I look for issues? I've confirmed that sex_type is created in both, main and repro projects. They are identical (checked using DBeaver). More than that the database structure is also completely identical. The database itself is created using dotnet ef database update.

P.S. No, ReloadTypes() doesn't help here either. Checked that already.

Provider version used: 6.0.3 PostgreSQL version used: 14.2

UPDATE_01

Made some experiments and this works:

var connection = (Npgsql.NpgsqlConnection)_medServiceDbContext.Database.GetDbConnection();
connection.Open();

using (var cmd = new Npgsql.NpgsqlCommand("SELECT c.\"Id\", c.\"Name\", c.\"SexType\" FROM public.\"Clients\" as c", connection))
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        var id = reader.GetFieldValue<int>(0);
        var surname = reader.GetFieldValue<string>(1);
        var sex = reader.GetFieldValue<SexType>(2);
        Console.WriteLine("{0}, {1}, {2}", id, surname, sex);
    }
}

Whereas this doesn't:

_medServiceDbContext.Clients.AsNoTracking().ToArray()

The latter gives the mentioned exception (Can't cast database type public.sex_type to Int32). My guess is that for some reason EF provider uses GetInt32() override of the NpgsqlDataReader implementation instead of GetFieldValue<T>().

UPDATE_02

I've actually finally managed to assemble a repro. It seems the issue is related to a usage of multiple different contexts targeting the same database connection. Described my findings here.

Answer 1

For anyone who will stumble into this, current workaround (until this issue is addressed) is to manually MapEnum all enums from all database contexts somewhere else, not inside of each DbContext static constructor as currently suggested in docs. For example inside static constructor for a Startup class of the app. Or something like that.