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?

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