Enum Type Mapping with .NET 9

[alainkaiser]

Hi guys

I justed created a minimal .NET 9 WebAPI project and wanted to test out the new enum type mapping with .net 9 and npgsql as described here:
https://www.npgsql.org/efcore/mapping/enum.html?tabs=with-connection-string%2Cwith-datasource

To share a little bit more about my setup:

DbContext

public class ApplicationDbContext : DbContext
{
    public DbSet<Initiative> Initiatives => Set<Initiative>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(ApplicationDbContext).Assembly);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(
            "User ID=admin;Password=admin;Server=localhost;Port=5435;Database=mydb;Include Error Detail=true;",
            o => o.MapEnum<InitiativeStatus>("initiative_status"));
    }
}

Model

public class Initiative
{
    public int Id { get; set; }

    public string Title { get; set; } = string.Empty;

    public string ShortDescription { get; set; } = string.Empty;

    public int Points { get; set; }

    public InitiativeStatus InitiativeStatus { get; set; } = InitiativeStatus.Draft;
}

public enum InitiativeStatus
{
    Draft = 10,
    DraftRejected = 11,
    Submitted = 12,
    Approved = 13,
    Published = 14,
    Archived = 15,
    Deleted = 16
}

Model Configuration with Data Seeding

public class InitiativeConfiguration : IEntityTypeConfiguration<Initiative>
{
    public void Configure(EntityTypeBuilder<Initiative> builder)
    {
        builder
            .ToTable("Initiative");

        builder
            .HasData([
                    new Initiative
                    {
                        Id = 1,
                        Title = "Initiative 1",
                        ShortDescription = "Short description 1",
                        Points = 10,
                        InitiativeStatus = InitiativeStatus.Draft
                    },
                    new Initiative
                    {
                        Id = 2,
                        Title = "Initiative 2",
                        ShortDescription = "Short description 2",
                        Points = 20,
                        InitiativeStatus = InitiativeStatus.DraftRejected
                    },
                    new Initiative
                    {
                        Id = 3,
                        Title = "Initiative 3",
                        ShortDescription = "Short description 3",
                        Points = 30,
                        InitiativeStatus = InitiativeStatus.Submitted
                    },
                    new Initiative
                    {
                        Id = 4,
                        Title = "Initiative 4",
                        ShortDescription = "Short description 4",
                        Points = 40,
                        InitiativeStatus = InitiativeStatus.Approved
                    },
                    new Initiative
                    {
                        Id = 5,
                        Title = "Initiative 5",
                        ShortDescription = "Short description 5",
                        Points = 50,
                        InitiativeStatus = InitiativeStatus.Published
                    },
                    new Initiative
                    {
                        Id = 6,
                        Title = "Initiative 6",
                        ShortDescription = "Short description 6",
                        Points = 60,
                        InitiativeStatus = InitiativeStatus.Archived
                    },
                    new Initiative
                    {
                        Id = 7,
                        Title = "Initiative 7",
                        ShortDescription = "Short description 7",
                        Points = 70,
                        InitiativeStatus = InitiativeStatus.Deleted
                    }
                ]
            );
    }
}

Test API Endpoint

[HttpGet("initiatives")]
    public async Task<IActionResult> GetInitiatives()
    {
        var initiativesPublishesOrGreater = await context.Initiatives
            .Where(i => i.InitiativeStatus >= InitiativeStatus.Published)
            .ToListAsync();
        
        return Ok(initiativesPublishesOrGreater);
    }

The goal of this Endpoint is to return all Initiatives which have at least the Status "Published". So far so good.
When I inspect the database, I see that a custom enum type is generated for the InitiativeStatus enum and that the values are stored as strings (see screenshot).

But the endpoint does not what it should do. When I request data from the endpoint, the data which comes back looks like this:

[
  {
    "id": 3,
    "title": "Initiative 3",
    "shortDescription": "Short description 3",
    "points": 30,
    "initiativeStatus": 12
  },
  {
    "id": 5,
    "title": "Initiative 5",
    "shortDescription": "Short description 5",
    "points": 50,
    "initiativeStatus": 14
  }
]

So as you can see, I get back Initiatives with the Status "Submitted" and "Published". I expected to get all Initiatives on which the Status is at least "Published" (Published, Archived, Deleted).

What is missing in my code to make this work? It is obvious that the comparison is made on the string values of the enum. For this reason, the initiatives are also returned with the status Submitted.

Thanks in advance for your feedback.

[alainkaiser]

Just tested something else: If I query all Initiatives first from the Database (without a filter) and then do the Filtering in Memory, it works as expected:

[HttpGet("initiatives")]
    public async Task<IActionResult> GetInitiatives()
    {
        var initiatives = await context.Initiatives
            .ToListAsync();
        
        var initiativesPublishesOrGreater = initiatives
            .Where(i => i.InitiativeStatus >= InitiativeStatus.Published)
            .ToList();
        
        return Ok(initiativesPublishesOrGreater);
    }

[roji]

But the endpoint does not what it should do. When I request data from the endpoint, the data which comes back looks like this.

This is likely some sort of issue with the JSON serialization side of your application - System.Text.Json by default serializes enums as strings (docs). EF's responsibility goes only so far as to materialize the .NET instances (Initiative instances); what happens beyond that is outside of EF's scope.

I'll go ahead and close this as unrelated to EF. If you can't manage to get this to work, I recommend posting a question on stackoverflow where people will help out. If you suspect a bug around this in EF, post back here with a minimal, runnable repro and we'll revisit.

[alainkaiser]

hei @roji, thanks for your feedback on this.
I think we have misunderstood each other a little. The problem is not that the enum values are not serialized as a string in the API response. To achieve this, you can simply configure a JsonStringEnumConverter, I am aware of that.

Something like this:

builder.Services.AddControllers().AddJsonOptions(x =>
{
    x.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
});

The "problem" occurs with this query here:

var initiativesPublishesOrGreater = await context.Initiatives
            .Where(i => i.InitiativeStatus >= InitiativeStatus.Published)
            .ToListAsync();

What I would expect here is the return of all Initiatives which have at least the status 'Published'. So:

• Published
• Archived
• Deleted

But what I get back is something different:

[
    {
        "id": 3,
        "title": "Initiative 3",
        "shortDescription": "Short description 3",
        "points": 30,
        "initiativeStatus": "Submitted"
    },
    {
        "id": 5,
        "title": "Initiative 5",
        "shortDescription": "Short description 5",
        "points": 50,
        "initiativeStatus": "Published"
    }
]

As you can see, on the database level, EF most likely makes a string comparison and does not consider the enum values (integers).

Here is the logged query:

info: 03.03.2025 14:03:43.157 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT i."Id", i."InitiativeStatus", i."Points", i."ShortDescription", i."Title"
      FROM "Initiative" AS i
      WHERE i."InitiativeStatus" >= 'published'::initiative_status

[roji]

@alainkaiser I see, I indeed completely misunderstood. To summarize, you're using a >= operation on enums; in C#, that's a numeric comparison on the enum's underlying interger value, but EF simply translates that to a >= in SQL between PostgreSQL enum types, which has a different meaning.

I unfortunately don't think there's really a possible solution here - this is very similar to e.g. applying >= on a value-converter property (dotnet/efcore#10434); EF has no knowledge of the database-side meaning of the operator. We could in theory provide specific logic for when comparison operators (e.g. >=) are applied to enums mapped to PG enums, but I'm not even quite sure what a good translation here would be - the C# behavior simply cannot be translated to PG, since PG has no knowledge of the underlying integer values of the .NET enum... So I think this scenario unfortunately is unsupported...

[alainkaiser]

@roji Thanks so much for your response, that's exactly what I meant.