Support [JsonPropertyName] in query translation

[kirillta]

I have following POCO and a jsonb field in a DB

public class Name
{
    public string De {get; set; }
    public string En {get; set; }
}

{"de": "Austria", "en": "Austria"}

if I query it this way I have null in my En field in a result

var countries = await _context.Countries
    .Select(c => new {c.Code, En = c.Name.En})
    .ToListAsync(cancellationToken);

So I need either change a property name, either a field key to get a non-nullable value. But could I use an attribute instead?

[roji]

Can you explain a bit more what you're asking exactly? Would would like an attribute for?

[kirillta]

This query translates to the following:

SELECT c."Code", c."Name"->>'En' AS "En"

where 'En' is from a name of the property Name.En. I would like to decouple the property name from the generated json field key. Like you can do it to tables with ColumnAttribute or to serialization with JsonPropertyNameAttribute

For example

public class Name
{
    public string De {get; set; }
    [JsonPropertyName("anotherName")]    
    public string En {get; set; }
}

will generate

SELECT c."Code", c."Name"->>'anotherName' AS "En"

[agoretsky]

same issue

[roji]

/cc @lauxjpn

[sreejith-ms]

Hi @roji

I'm facing the same issue, if it is already fixed is there any settings to apply?
I'm using version: 3.1.11
Previously I thought the issue will resolve if I set the column name of the entity using fluent API, but it is not working.
https://stackoverflow.com/a/66799628/5078531

[roji]

@sreejith-ms this was done for 5.0.0, so [JsonPropertyName] is respected there. We generally don't backport new features to patch versions of already-released versions (like 3.1).

[sreejith-ms]

@roji I upgraded the versions and tested, but the issue still exists.

Npgsql.EntityFrameworkCore.PostgreSQL: 5.0.2
Npgsql: 5.0.4

[JsonProperty(PropertyName = "foo_id")]
public string Id { get; set; }

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (168ms) [Parameters=[@__ToString_0='?'], CommandType='Text', CommandTimeout='30']
SELECT f.key, f.value
FROM foo AS f
WHERE f.value->>'Id' = @__ToString_0
LIMIT 1

[roji]

@sreejith-ms can you please post a runnable code sample?

[sreejith-ms]

@roji The issue is resolved when I removed NpgsqlConnection.GlobalTypeMapper.UseJsonNet(); from startup.
Why UseJsonNet disables JsonPropertyName attribute?

public void ConfigureServices(IServiceCollection services)
{
    NpgsqlConnection.GlobalTypeMapper.UseJsonNet();
    services.AddControllers();
    services.AddDbContext<BloggingContext>(options =>
    options.UseNpgsql(Configuration.GetConnectionString("BloggingContext")));
}

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

    public DbSet<Entity<Blog>> Blogs { get; set; }
   
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entity<Blog>>(entity =>
        {
            entity.HasKey(e => e.Key)
                .HasName("blogid_pkey");

            entity.ToTable("blog", "my_db");

            entity.Property(e => e.Key)
                .HasColumnName("key")
                .ValueGeneratedNever();

            entity.Property(e => e.Value)
                .IsRequired()
                .HasColumnName("value")
                .HasColumnType("jsonb");
        });
    }
}

public class Entity<TValue>
{
    public Guid Key { get; set; }

    public TValue Value { get; set; }
}

public class Blog
{
    [JsonPropertyName("blog_id")]
    public string BlogId { get; set; }
    public string Url { get; set; }

}

[HttpGet]
public async Task<Blog> Test()
{
    var key = Guid.NewGuid();
    var entity = bloggingContext.Blogs.Add(new Entity<Blog>
    {
        Key = key,
        Value = new Blog
        {
            BlogId = key.ToString(),
            Url = "foo"
        }
    }).Entity;
    await bloggingContext.SaveChangesAsync();
    var blogs = await bloggingContext.Blogs.Where(x => x.Value.BlogId == entity.Value.BlogId).FirstOrDefaultAsync();
    return entity.Value;
}

<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.4"/>
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.4" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.2" />
<PackageReference Include="Npgsql.Json.NET" Version="5.0.4" />

[roji]

@sreejith-ms although Json.NET is supported at the ADO.NET level (as in the docs), at the EF Core provider level only System.Text.Json is supported. So I'm not sure why you're seeing that specific issue and why it occurs only with UseJsonNet, but UseJsonNet should not be specified in an EF Core application.

[lauxjpn]

I just checked the code for Pomelo, but it looks like we had already implemented it for both System.Text.Json (MySqlJsonNewtonsoftPocoTranslator.cs) and Json.NET (MySqlJsonMicrosoftPocoTranslator.cs).

[sreejith-ms]

@roji The team is currently using ADO.NET and migrating to EFCore, that's why UseJsonNet() is used.
Another thing is our models are decorated with JsonPropertyAttribute from Json.NET.
Any chance for supporting JsonPropertyAttribute like @lauxjpn pointed out?

[roji]

@sreejith-ms there haven't been any requests up to now for Json.NET support at the EFCore.PG level - this is the first time - most people seem to be OK with System.Text.Json. Note that S.T.J is also fully supported at the ADO.NET level (without any plugins), so you can switch to that at the ADO.NET level as well - the transition from Json.NET to S.T.J could be trivial, depending on exactly which features of Json.NET you're using.

[lauxjpn]

@sreejith-ms I believe that Pomelo is the only major provider that currently has Json.NET support. However, when it comes to features, a number of missing common ones have been added to S.T.J with .NET 5, which closes the gap between Json.NET and S.T.J to some degree.

[sreejith-ms]

Thanks @roji and @lauxjpn. I will start migrating to S.T.J.