How to apply naming convention to json properties?

[Arsync]

I have models like

public class Employee
{
    public EmployeeSettings Settings { get; set; }
}

public class EmployeeSettings
{
    public string DefaultArea { get; set; }
}

Query for data:

dbContext.Employees.FirstOrDefault(x => x.Settings.DefaultArea == "admin");

And with .UseSnakeCaseNamingConvention() it still looks like this in SQL:

AND (e.settings->>'DefaultArea' = 'admin')

but needed:

AND (e.settings->>'default_area' = 'admin')

I've tried even this (without success):

public class EmployeeMap : IEntityTypeConfiguration<Employee>
{
    public void Configure(EntityTypeBuilder<Employee> b)
    {
        var serializerSettings = new JsonSerializerSettings
        {
            NullValueHandling = NullValueHandling.Ignore,
            ContractResolver = new DefaultContractResolver
            {
                NamingStrategy = new SnakeCaseNamingStrategy()
            }
        };

        b.Property(e => e.Settings)
            .HasColumnType("jsonb")
            .HasConversion(
                v => JsonConvert.SerializeObject(v, serializerSettings),
                v => JsonConvert.DeserializeObject<EmployeeSettings>(v, serializerSettings)
            );
    }
}

[roji]

@Arsync properties inside JSON documents aren't covered by this plugin, at least not yet. The way JSON support is currently implemented, EF Core is completely unaware of everything happening inside a JSON document (it isn't part of the EF model), so this plugin can't rewrite any names.

The way to manage this would be via the JsonSerializerOptions of System.Text.Json, which is used to actually serialize/deserialize JSON documents. Setting in the PG EF provider JsonSerializerOptions isn't currently supported out of the box either (npgsql/efcore.pg#1107), but see this workaround which should provide a solution.

Am going to close this for now, but will reopen if JSON support changes in a way that makes it relevant.

[Arsync]

Tried to override mapping for NpgsqlDbType.Jsonb with JsonSerializerOptions naming policy changed to SnakeCaseNamingPolicy, but select and where queries still looks like e.settings->>'DefaultArea'. Perhaps, I've missed something?

Following code not worked:

private static void OverrideJsonType(NpgsqlDbType type)
{
    var origJsonbMapping =
        NpgsqlConnection.GlobalTypeMapper.Mappings.Single(m => m.NpgsqlDbType == type);

    NpgsqlConnection.GlobalTypeMapper.RemoveMapping(origJsonbMapping.PgTypeName);
    NpgsqlConnection.GlobalTypeMapper.AddMapping(new NpgsqlTypeMappingBuilder
    {
        PgTypeName = origJsonbMapping.PgTypeName,
        NpgsqlDbType = origJsonbMapping.NpgsqlDbType,
        DbTypes = origJsonbMapping.DbTypes,
        ClrTypes = origJsonbMapping.ClrTypes,
        InferredDbType = origJsonbMapping.InferredDbType,
        TypeHandlerFactory = new JsonbHandlerFactory(new JsonSerializerOptions
        {
            PropertyNamingPolicy = new SnakeCaseNamingPolicy()
        })
    }.Build());
}

Used as:

b.UseNpgsql(GetDefaultConnectionString(), opts =>
    {
        opts.UseNodaTime();

        OverrideJsonType(NpgsqlDbType.Json);
        OverrideJsonType(NpgsqlDbType.Jsonb);
        OverrideJsonType(NpgsqlDbType.JsonPath);
    })
    .UseSnakeCaseNamingConvention();

Still got queries like

SELECT e.settings->>'DefaultArea' AS "DefaultArea"
...

[roji]

You're right - I didn't mention that the JsonSerializerOptions apply when System.Text.Json is loading or saving the JSON document to the database, but not when constructing queries that drill into it. For this scenario, the only option you currently have is to specify [JsonPropertyName] on your properties - the EF provider does respect that. It's not a good solution, but it's the only onle until npgsql/efcore.pg#1107 is done.