Issue with jsonb, enum mapping and querying

[coadan]

I'm getting the following: Npgsql.PostgresException : 22P02: invalid input value for enum "CountryISO3": "166" when doing LINQ-queries with JSONB columns.

The reproduction steps are in this project. Just run the CreateSchema.sql and fix the connection string:
Npgsql.EnumBugger.zip

What I would expect is to be able to do queries with the CountryISO3 enum in a JSONB column even though it is also in the mapped as a type in the database.

Schema:

CREATE TYPE "CountryISO3" AS ENUM ( 'NOR' );

CREATE TABLE public."Bugger"(
      "Id" BIGSERIAL NOT NULL,
      "Contact" jsonb NOT NULL,
      CONSTRAINT "PK_Bugger" PRIMARY KEY ("Id")
  );

The program.cs in the zip to reproduce:

internal static class Program
    {
        private static async Task Main()
        {
            using (var ctx = new TestContext())
            {
                TestContext.AddEnum<CountryISO3>();
                ctx.Bugger.Add(new Bugger { Contact = new Contact { Country = CountryISO3.NOR } });
                await ctx.SaveChangesAsync();
            }

            try
            {
                using (var ctx2 = new TestContext())
                {
                    await ctx2.Bugger.FirstOrDefaultAsync(x => x.Contact.Country == CountryISO3.NOR);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            Console.ReadLine();
        }
    }

    public class TestContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql("Host=localhost;Database=testdb;Username=postgres;Password=");

        public DbSet<Bugger> Bugger { get; set; }

        public static void AddEnum<T>() where T : struct, Enum
        {
            NpgsqlConnection.GlobalTypeMapper.MapEnum<T>(nameTranslator: new EfEnumTranslator());
        }

        private class EfEnumTranslator : INpgsqlNameTranslator
        {
            public string TranslateMemberName(string clrName)
            {
                return clrName;
            }

            public string TranslateTypeName(string clrName)
            {
                return $"public.{clrName}";
            }
        }
    }

    public class Bugger
    {
        public long Id { get; set; }

        [Column(TypeName = "jsonb")]
        public Contact Contact { get; set; }
    }

    public class Contact
    {
        public CountryISO3 Country { get; set; }
    }

    public enum CountryISO3
    {
        NOR = 166
    }
}

[roji]

What I would expect is to be able to do queries with the CountryISO3 enum in a JSONB column even though it is also in the mapped as a type in the database.

How would you expect it to be mapped, as an integer, as text? Why one and not the other?

[YohDeadfall]

There are user provided options where a converter is stored. What you need is to invoke GetConverter on the options and get back the converter. In case of an enumeration the method always returns a non-null result.

[coadan]

What I would expect is to be able to do queries with the CountryISO3 enum in a JSONB column even though it is also in the mapped as a type in the database.

How would you expect it to be mapped, as an integer, as text? Why one and not the other?

I would actually like to store it as a text when writing jsonb to the database, but I found no way to override the default behavior of converting it to an integer in the serialization step. Is this possible?

There are user provided options where a converter is stored. What you need is to invoke GetConverter on the options and get back the converter. In case of an enumeration the method always returns a non-null result.

I'm not sure what you mean. Which options do I get the converter from? And how would I use that to solve this?

Sorry. I'm not overly familiar with Npgsql or Postgres, so if there is a way to configure it in such a way that this does not become an issue I would be happy to do so.

[roji]

@YohDeadfall what's the status here? Is this simply a dup of #1107, since we don't currently expose access to JSON serialization options?

[YohDeadfall]

Yes, that's the problem.

[roji]

Duplicate of #1107