Skip to content

Allow mapping JSON to Dictionary<string,string> #1825

Description

@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.

Activity

  1. changed the title [-]Order by `jsonb` for key[/-] [+]Allow mapping JSON to Dictionary<string,string>[/+] on Apr 26, 2021
  2. roji commented on Apr 26, 2021

    @roji
    Member

    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>.

  3. added this to the Backlog milestone on Apr 26, 2021
  4. rlightner commented on Aug 7, 2023

    @rlightner

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

  5. roji commented on Aug 7, 2023

    @roji
    Member

    @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.

  6. rlightner commented on Aug 7, 2023

    @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))!);
    
  7. roji commented on Aug 7, 2023

    @roji
    Member

    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).

  8. totpero commented on Nov 30, 2023

    @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);
        });
    
  9. roji commented on Nov 30, 2023

    @roji
    Member

    Note that mapping Dictionaries is not support when using ToJson and Owns (see dotnet/efcore#29825).

  10. avisra commented on Jun 12, 2024

    @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

  11. roji commented on Jun 12, 2024

    @roji
    Member

    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.

  12. added theissue type on Jun 14, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions