Querying enum properties inside JSONB columns fails when using JsonStringEnumConverter

Aperta
  • Qualcosa non va?
#3,784 4 commenti 2 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
48/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Tranquilla
Stack tecnologico
csharp, postgresql
Ambito
databases

Direzione di ricerca

Start by running the provided reproduction against PostgreSQL, then read the provider's JSONB query translation and related tests. Trace how the enum property's configured JSON converter affects server-side comparisons; done means the Where query compares the stored string value without an integer cast and the relevant tests pass.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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 [email protected]
#:package [email protected]
#: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
Lingua principale
C#
Stelle
1.8k
Fork
268
Merge medio
2h 35m
PR unite (30g)
136

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di npgsql/efcore.pg

Tutte le issue di npgsql/efcore.pg

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.