Allow mapping JSON to Dictionary<string,string>

[sneltyn]

Hello! I have question. I used next entity with jsonb type column:

class Entity
{
    public int Id {get;set;}
    
    [Column(TypeName = "jsonb")]
    public Dictionary<string, string> Name {get;set;}  
}

I want to use order by for example how Postgres SQL script:

SELECT "Id", "Name" FROM public."Entities" ORDER BY "Name"->>'en' DESC

Is it possible to do this sorting on EF? I searched in the documentation for this, as well as on the vastness of stackoverflow and in issues github, but I did not find an answer to this question.

[roji]

The provider currently doesn't support mapping Dictionary<string,string> to JSON; you can either map a .NET POCO (for strongly-typed documents with a fixed schema) or JsonDocument (for weakly-typed documents with varying schemas). See the docs for more details. If you use one of these two mapping techniques, you can generate a JSON field lookup in an ORDER BY just as you would in a WHERE clause.

Keeping open to track mapping Document<string,string>.

[rlightner]

@roji Any chance there was movement on this since 2021, or a way to implement this?

[roji]

@rlightner the main reason I haven't been looking at this is the steady progress on JSON support at the EF side - it would be best to not do something specific here on the PG side but rather to have one holistic approach across EF. For now, you can use value converters to serialize your Dictionary to a JSON document, and then use SQL querying if you need to query inside that document.

[rlightner]

Thanks for the fast response. To clarify: Something like this?

    [Column("key_values", TypeName = "jsonb")]
    public Dictionary<string, string> KeyValues{ get; set; }

                entity.Property(p => p.KeyValues)
                      .HasColumnType("jsonb")
                      .HasConversion(
                        v => JsonSerializer.Serialize(v, new JsonSerializerOptions(JsonSerializerDefaults.General)),
                        v => JsonSerializer.Deserialize<Dictionary<string, string>>(v, new JsonSerializerOptions(JsonSerializerDefaults.General))!);

[roji]

Yep - that should take care of serialization. The only thing this doesn't take care of is querying (e.g. filtering based on the value inside the dictionary).

[totpero]

I use something like this for Dictionary<string, string>:

builder.OwnsOne(
    q => q.Name, ownedNavigationBuilder =>
    {
        ownedNavigationBuilder.ToJson("jsonb");
        ownedNavigationBuilder.Ignore(x => x.Capacity);
        ownedNavigationBuilder.Ignore(x => x.Count);
    });

[roji]

Note that mapping Dictionaries is not support when using ToJson and Owns (see https://github.com/dotnet/efcore/issues/29825).

[avisra]

@roji I'm just now getting around to upgrading my project to .NET 8.

I see that POCO mapping was deprecated. Am I correct in my understanding that this means I can no longer use [Column(TypeName = "jsonb")] on collections? Is there a way to continue using it for things like this? I was using the [Column(TypeName = "jsonb")] for dictionaries just fine, but due to the deprecation, had to switch most things to ToJson. And now I'm in refactoring hell due to bugs in .NET 8 (e.g. the Id property bug) and missing features in .NET 8 like this dictionary support missing

[roji]

The older way of mapping JSON via [Column(TypeName = "jsonb")] isn't deprecated, where are you seeing that it can "no longer be used"? This method is just no longer the recommended way going forward. When something isn't supported via ToJson() - like weakly-typed mapping via Dictionary, using [Column(TypeName = "jsonb")] should be fine.