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 npgsql/efcore.pg#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: npgsql/efcore.pg#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: Mapping attribute for to specify a value converter type #29426

[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