Mapping to POCOs with top-level dictionary from Postgres jsonb column

Question

I have a .NET Core 3.1 application using EF Core and a Postgres database. In the database I have one jsonb column that I now want to map to a well-defined set of classes in EF Core.

The content of the jsonb column looks like the following:

{
    "entry1": {
        "name": "entry1",
        "contents": {
            "entry1.1": {
                "name": "entry1.1"
            },
            "entry1.2": {
                "name": "entry1.2",
                "contents": {
                    "entry1.2.1": {
                        "name": "entry1.2.1"
                    }
                }
            }
        }
    }
}

At the top level it is a dictionary mapping strings to entries. Each entry has a name and can have contents, which is again a dictionary mapping strings to entries.

public class Entry
{
    public string name { get; set; }
    public Dictionary<string, Entry> contents { get; set; }
}

The jsonb column itself is defined on a table like this:

public class MyTable {
    [Column(TypeName = "jsonb")]
    public Dictionary<string, Entry> Entries { get; set; }
}

The problem with this now is that it simply doesn't work. When I fetch an entry from the database with EF Core, the "Entries" property does contain indeed a dictionary with a single key "entry1", but the value of that key is an empty Entry object (name and contents are both null).

The Npgsql documentation on mapping jsonb columns to POCOs doesn't explain how to handle dictionaries in this case. I couldn't find any examples with a top-level dictionary in the jsonb column, so I'm not entirely sure I'm doing this right.

How can I wire this up correctly so that my jsonb column gets mapped to a dictionary of Entry objects?

From my current experience with Npgsql, it seems the mapping has to be fairly concrete. Meaning your POCO's will have to match the keys you provide in the json. If it were me, i would probably read the JSON in as a whole string and then parse it with a helper/service. It seems like the trouble would come in the cascading of your "entry" objects. — Ginger Ninja
– Ginger Ninja, Commented May 2, 2020 at 6:19
@GingerNinja it works out of the box if the property is a list or the Entry class directly, only the Dictionary doesn't work. I'm not sure if this is simply one case EF Core npgsql didn't implement, or if I'm doing something wrong here. I found how to manually define serialization just now, and it works even with the recursive links between the Entry objects. But as far as I understand this version of doing things is more limited (e.g. no query support) — Mad Scientist
– Mad Scientist, Commented May 2, 2020 at 17:36

Shay Rojansky · Accepted Answer · 2020-05-04 11:27:58Z

1

The following seems to work well:

class Program
{
    static void Main(string[] args)
    {
        using (var createCtx = new BlogContext())
        {
            createCtx.Database.EnsureDeleted();
            createCtx.Database.EnsureCreated();

            createCtx.Blogs.Add(new Blog
            {
                Entries = new Dictionary<string, Entry>
                {
                    { "bla", new Entry { Foo = "foo1" }}
                }
            });

            createCtx.SaveChanges();
        }

        using var ctx = new BlogContext();

        var results = ctx.Blogs.Single();
        Console.WriteLine(results.Entries["bla"].Foo);
    }
}

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Database=test;Username=npgsql_tests;Password=npgsql_tests")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

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

public class Entry
{
    public string Foo { get; set; }
}

In the database:

test=# select * from "Blogs"
test-# ;
 Id | Name |         Entries          
----+------+--------------------------
  1 |      | {"bla": {"Foo": "foo1"}}
(1 row)

answered May 4, 2020 at 11:27

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mad Scientist Over a year ago

That's weird, it looks pretty much exactly like what I was trying to do, I can't see any relevant difference right now. I'll have to compare this more carefully with my own version to see what I did differently. Thanks for providing a full self-contained example, that will be really helpful to experiment with.

Collectives™ on Stack Overflow

Mapping to POCOs with top-level dictionary from Postgres jsonb column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related