Querying enum properties inside JSONB columns fails when using JsonStringEnumConverter

[brendonparker]

Description

When a JSONB column contains a property typed as a C# enum, and JsonStringEnumConverter is configured via ConfigureJsonOptions, any server-side query that references the enum property fails with:

Npgsql.PostgresException: 22P02: invalid input syntax for type integer: "Good"

Npgsql generates SQL that casts the extracted JSON value to integer (the enum's backing type) rather than comparing it as text, even though JsonStringEnumConverter is configured and the JSON stores the enum as its string name.

Materializing the full entity via ToListAsync() works fine — the issue is specifically with server-side evaluation of the enum property (e.g. in Where, GroupBy, OrderBy, etc.).

Steps to Reproduce

Minimal reproduction using dotnet run (requires .NET 10+, PostgreSQL):

#:package Npgsql.EntityFrameworkCore.PostgreSQL@10.0.0
#:package EFCore.NamingConventions@10.0.1
#:property PublishAot=false

using System.Text.Json;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;

var dbContext = new DemoContext(
    "Host=localhost;Database=repro_db;Username=postgres;Password=postgres"
);

dbContext.Database.ExecuteSql(
    $$"""
    drop table if exists parents;
    create table parents
    (
        id   integer not null,
        data jsonb not null
    );

    insert into parents (id, data) values
    (1, '{"Status": "Good"}'),
    (2, '{"Status": "Good"}'),
    (3, '{"Status": "Bad"}'),
    (4, '{"Status": "Good"}');
    """
);

// This works — client-side deserialization handles string enums correctly
var resultsThatWork = await dbContext.Parents.ToListAsync();
foreach (var result in resultsThatWork)
{
    Console.WriteLine(result);
}

// This fails — server-side evaluation casts "Good" to integer
var goodResults = await dbContext.Parents
    .Where(x => x.Data.Status == Status.Good)
    .ToListAsync();

foreach (var result in goodResults)
{
    Console.WriteLine(result);
}

class DemoContext(string connectionString) : DbContext()
{
    public DbSet<Parent> Parents => Set<Parent>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>(entity =>
        {
            entity.Property(x => x.Data).HasColumnType("jsonb");
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseNpgsql(
                connectionString: connectionString,
                optionsBuilder =>
                {
                    optionsBuilder.ConfigureDataSource(dataSourceBuilder =>
                    {
                        dataSourceBuilder
                            .ConfigureJsonOptions(
                                new JsonSerializerOptions
                                {
                                    Converters = { new JsonStringEnumConverter() },
                                }
                            )
                            .EnableDynamicJson();
                    });
                }
            )
            .UseSnakeCaseNamingConvention();
    }
}

class Parent
{
    public required long Id { get; set; }
    public required ThisIsJson Data { get; set; }
}

class ThisIsJson
{
    public required Status Status { get; set; }
}

enum Status
{
    Good,
    Bad,
}

Expected Behavior

The Where clause should generate SQL that compares the extracted JSON value as text, e.g.:

SELECT ... FROM parents AS p WHERE p.data->>'Status' = 'Good'

Actual Behavior

The generated SQL casts the extracted value to integer, causing PostgreSQL to fail:

Npgsql.PostgresException (0x80004005): 22P02: invalid input syntax for type integer: "Good"

Environment

• Npgsql.EntityFrameworkCore.PostgreSQL: 10.0.0
• .NET: 10
• PostgreSQL: 17.5

[brendonparker]

I'm able to work around this by adding a postgres enum.

So adding this to my ddl script:

drop type if exists status;
create type status as enum ('Good', 'Bad');

and these changes to the OnConfiguring:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseNpgsql(
            connectionString: connectionString,
            optionsBuilder =>
            {
                // Add this line
                optionsBuilder.MapEnum<Status>(nameTranslator: CustomNameTranslator.Instance);
                optionsBuilder.ConfigureDataSource(dataSourceBuilder =>
                {
                    dataSourceBuilder
                        .ConfigureJsonOptions(
                            new JsonSerializerOptions
                            {
                                Converters = { new JsonStringEnumConverter() },
                            }
                        )
                        .EnableDynamicJson();
                });
            }
        )
        .UseSnakeCaseNamingConvention();
}

with this INpgsqlNameTranslator:

internal class CustomNameTranslator : INpgsqlNameTranslator
{
    public static readonly CustomNameTranslator Instance = new();
    private readonly NpgsqlSnakeCaseNameTranslator _translator = new();

    // We want to translate the enum type to be more pgsql standard
    public string TranslateTypeName(string clrName) => _translator.TranslateTypeName(clrName);

    // But we want to store the enums in the database as they appear in code (not snake case)
    public string TranslateMemberName(string clrName) => clrName;
}

Which seems super clunky

[brendonparker]

Another work around: override the ConfigureConventions in the DbContext

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<Status>.HaveConversion<string>();
}

[brianpursley]

@brendonparker Since you are using EF, are you able to use ToJson POCO mapping? Do you need to use EnableDynamicJson?

Here is your example updated, which works for me, no postgres enum needed.

#:package Npgsql.EntityFrameworkCore.PostgreSQL@10.0.0
#:package EFCore.NamingConventions@10.0.1
#:property PublishAot=false

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

var dbContext = new DemoContext(
    "Host=localhost;Database=repro_db;Username=postgres;Password=postgres"
);

dbContext.Database.ExecuteSql(
    $$"""
    drop table if exists parents;
    create table parents
    (
        id   integer not null,
        data jsonb not null
    );

    insert into parents (id, data) values
    (1, '{"Status": "Good"}'),
    (2, '{"Status": "Good"}'),
    (3, '{"Status": "Bad"}'),
    (4, '{"Status": "Good"}');
    """
);

var resultsThatWork = await dbContext.Parents.ToListAsync();
foreach (var result in resultsThatWork)
{
    Console.WriteLine(result);
}

var goodResults = await dbContext.Parents
    .Where(x => x.Data.Status == Status.Good)
    .ToListAsync();

foreach (var result in goodResults)
{
    Console.WriteLine(result);
}

class DemoContext(string connectionString) : DbContext()
{
    public DbSet<Parent> Parents => Set<Parent>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>(entity =>
        {
            entity.ComplexProperty(c => c.Data, d => d.ToJson());
        });
    }

    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder.Properties<Status>().HaveConversion<string>();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseNpgsql(connectionString)
            .UseSnakeCaseNamingConvention()
            .LogTo(Console.WriteLine, LogLevel.Information); // Just for debugging, to see the generated SQL
    }
}

class Parent
{
    public required long Id { get; set; }
    public required ThisIsJson Data { get; set; }
}

class ThisIsJson
{
    public required Status Status { get; set; }
}

enum Status
{
    Good,
    Bad,
}

It is able to handle the enum as string...

info: 2/19/2026 11:17:53.204 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT p.id, p.data
      FROM parents AS p
      WHERE (p.data ->> 'Status') = 'Good'

Optionally, instead of configuring the conversion in ConfigureConventions() you can use the Column data annotation:

    [Column(TypeName = "text")]
    public required Status Status { get; set; }