JSON: Support [JsonStringEnumConverter]

[roji]

System.Text.Json has [JsonStringEnumConverter], which maps CLR enums to strings in the JSON document.

I'm not sure if we plan to support arbitrary EF value converters for JSON properties (we should probably open an issue for that). If we do, then we could set up the appropriate enum/string converter when we see this attribute.

Originally requested for Npgsql by @sergeyshaykhullin in https://github.com/npgsql/efcore.pg/issues/2325

[sergeyshaykhullin]

@roji I think that enum property with .HasConversion<string>() should also translate to CAST(p.barcode->>'type' AS text)

[andremantaswow]

@roji Is there any workaround for this? We need enums as strings stored in DB.

The docs say:

By default, any enum properties in your model will be mapped to database integers. EF Core 2.1 also allows you to map these to strings in the database with value converters

Tried using 3.0, 5.0 and 6.0 (with their respective EF versions), lots of different combinations but nothing seems to work.

Setup:

[Table("order")]
public class OrderEntity
{
	[Column("id")]
	public Guid Id { get; set; }

	[Column("entity", TypeName = "jsonb")]
	public Order Model { get; set; }
}

public class Order
{
	[JsonPropertyName("state")]
	[JsonConverter(typeof(JsonStringEnumConverter))]
	// tried with and without
	[Column("state", TypeName = "nvarchar(50)")]
	public OrderState State { get; set; }

	// ...
}

public class TestDbContext : DbContext
{
	public TestDbContext(DbContextOptions<TestDbContext> options)
		: base(options)
	{
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
	}

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		base.OnModelCreating(modelBuilder);
		
		// attempt a)
		modelBuilder.Entity<Order>()
			.Property(x => x.State)
			.HasConversion(new EnumToStringConverter<OrderState>());
			
		// attempt b)
		modelBuilder.Entity<Order>()
			.Property(x => x.State)
			.HasConversion<string>();
	}

	public DbSet<OrderEntity> Orders { get; set; }
}

var try1 = await DbContext.Orders
	.Where(x => x.Model.State == OrderState.Acknowledged)
	.FirstOrDefaultAsync();

// as per https://stackoverflow.com/a/69994513
var filter = new[] { OrderState.Acknowledged };
var try2 = await DbContext.Orders
	.Where(x => filter.Contains(x.Model.State))
	.FirstOrDefaultAsync();

Error is always the same: Npgsql.PostgresException: 22P02: invalid input syntax for type integer: "acknowledged"

Compiling query expression: 
      'DbSet<OrderEntity>()
      .Where(x => (int)x.Model.State == 0)
      .FirstOrDefault()'
...
Generated query execution expression: 
'queryContext => ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync<OrderEntity>(
  asyncEnumerable: new SingleQueryingEnumerable<OrderEntity>(
	  (RelationalQueryContext)queryContext, 
	  RelationalCommandCache.SelectExpression(
		  Projection Mapping:
			  EmptyProjectionMember -> Dictionary<IProperty, int> { [Property: OrderEntity.Id (no field, Guid) Required PK AfterSave:Throw ValueGenerated.OnAdd, 0], [Property: OrderEntity.Model (Order), 1] }
		  SELECT TOP(1) o.id, o.entity
		  FROM order AS o
		  WHERE CAST(o.entity#>>{'state'} AS integer) == 0), 
	  Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, OrderEntity>, 
	  EntityFramework.Jsonb.Tests.TestDbContext, 
	  False, 
	  False, 
	  True
  ), 
  cancellationToken: queryContext.CancellationToken)'
...
Failed executing DbCommand (1,571ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT o.id, o.entity
      FROM "order" AS o
      WHERE CAST(o.entity->>'state' AS integer) = 0
      LIMIT 1

[roji]

@andremantaswow I'm not sure whether [JsonStringEnumConverter] makes sense as you're using it: from what I can tell, this attribute is meant to be used on enum properties inside a JSON document, to control how those properties are serialized to JSON. You're applying the attribute on the top-level property itself, so there's no JSON anywhere here, or am I missing something?

If this is indeed unrelated to JSON, and you're simply trying to map a .NET enum property to a string column in the database, then you can use a simple value converter for that (see this example in our docs). Otherwise, please provide a bit more context and explain what you're trying to achieve.

Note also that this issue is about the upcoming general JSON support in EF Core, and so isn't really related to the existing JSON support in the Npgsql provider specifically. For questions on that, please open an issue on https://github.com/npgsql/efcore.pg.

[andremantaswow]

@roji thanks for the reply.

My State property is on the Order class which is a jsonb column on the database. OrderEntity class is the root (the table) and has a Model property that is of type a Order.

I've tried all approaches on that url but they don't seem work with the postgres adapter.

~~I'll look for an issue on that repo or open a new one.~~

Edit: nevermind, I forgot that I reach this issue precisely from the one on the other repo: https://github.com/npgsql/efcore.pg/issues/2325

So according to you @roji, this is where the real issue is 😄

[roji]

@andremantaswow ah OK, I misread your code.

So yes, EF 7.0 is planned to include new JSON support, which the PG provider should align to (and which should be better in various ways) - this is why I'm not really evolving Npgsql's support at the moment, but rather waiting for things to be done on the EF side first. Having said that, it's unlikely for this feature specifically to make it in for 7.0 (it's in the backlog).

[ajcvickers]

Notes from triage:

• Enums are stored as strings by default in JSON documents. This is because the type mapping uses EnumToStringConverter by default.
• Enums can be stored as numbers by configuring the enum-to-number converter instead.
• An attribute for changing the value converter could be useful here: https://github.com/dotnet/efcore/issues/29426

[tparvi]

We ran into this exact issue. The enums are stored as strings inside JSONB document but queries fail because query contains CAST to integer which fails. Any idea when this could be supported?

[roji]

@tparvi EF 8.0 is changing the enum representation within JSON documents to be int instead of string; see this breaking change note.

[SimonCropp]

Enums are stored as strings by default in JSON documents. This is because the type mapping uses EnumToStringConverter by default.

note as of v8 this is no longer true https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#enums-as-ints

[A-Stapleton]

Would this support mapping a List<enum> to a json column as strings?

[roji]

@A-Stapleton it's already possible to map a List<MyEnumType> - please try it and file a separate issue if you run into trouble.

[A-Stapleton]

@roji Just want to check before making a new issue. Should this be able to work for a List<MyEnumType> while mapping the enum values as string?

[roji]

@A-Stapleton why not just try it out on a minimal program?

[A-Stapleton]

@roji I have got a minimal repro ready but I wanted to confirm that you expected it to work before I wrote it all up into a new issue.

I've read through https://devblogs.microsoft.com/dotnet/announcing-ef8-preview-4/ and according to https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#enums-as-ints it should work when a HasConversion<string>() was added but it doesn't appear to be, still maps it as an int.

Just thought I'd confirm before wasting anyone's time with a more complex issue 😃

[roji]

@A-Stapleton this definitely works - but you do need to configure the value converter on the primitive collection's element properly; see below for a code sample that works for me. If you run into other trouble related to this, please open a new issue.

Working code sample

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var blog = new Blog { MyEnums = [MyEnum.One, MyEnum.Three] };
context.Add(blog);
await context.SaveChangesAsync();

_ = await context.Blogs.ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .PrimitiveCollection(b => b.MyEnums).ElementType().HasConversion<string>();
    }
}

public class Blog
{
    public int Id { get; set; }
    public string? Name { get; set; }
    public List<MyEnum> MyEnums { get; set; }
}

public enum MyEnum { One, Two, Three }

[A-Stapleton]

@roji Excellent! thank you so much for the code sample. I can see where it's gone wrong. All of the examples I could find in the EF docs show configuration like this so that's what I was trying.

modelBuilder.Entity<Blog>()
            .Property(b => b.MyEnums)
            .HasConversion<string>();

I can confirm that does indeed work as intended when the correct config is used. Thank you so much for clarifying 😄