Cannot serialize Dictionary<string, string> in a JSON column

[Eirenarch]

I seem to be unable to serialize dictionaries in a JSON column with the new JSON support for SQL Server

using System.Text.Json;
using Microsoft.EntityFrameworkCore;

using TestContext context = new();

Thing thing1 = new()
{
    Data = new Dictionary<string, object> { { "test", "1" } },
    Child = new ChildThing
    {
        Name = "Foo",
        Data = new Dictionary<string, string> { { "test", "2" } }
    }
};

Console.WriteLine(JsonSerializer.Serialize(thing1)); //just test that the object can be serialized to JSON outside EF

context.Things.Add(thing1);
context.SaveChanges();

using TestContext context2 = new();
List<Thing> things = context2.Things.ToList();
foreach (Thing thing in things)
{
    Console.WriteLine($"Thing: {thing.ThingId},\n Thing.Data:{thing.Data.First().Key}:{thing.Data.First().Value}\n Child.Name: {thing.Child!.Name}, Child.Data.Count: {thing.Child?.Data?.Count}");
}

public class TestContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer("Data Source=.\\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True;MultipleActiveResultSets=true;TrustServerCertificate=True");
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Thing>()
            .OwnsOne(thing => thing.Child, ownedNavigationBuilder =>
            {
                ownedNavigationBuilder.ToJson();
                ownedNavigationBuilder.OwnsOne(child => child.Data);
            });

        builder.Entity<Thing>()
            .OwnsOne(thing => thing.Data, ownedNavigationBilder =>
            {
                ownedNavigationBilder.ToJson();
            });

        base.OnModelCreating(builder);
    }

    public DbSet<Thing> Things { get; set; } = null!;
}

public class Thing
{
    public int ThingId { get; set; }
    public ChildThing? Child { get; set; }
    public Dictionary<string, object> Data { get; set; } = null!;
}

public class ChildThing
{
    public string Name { get; set; } = null!;
    public Dictionary<string, string>? Data { get; set; }
}

The database which I created manually via SQL has one table Things with columns ThingId (identity, int), Data (nvarchar(4000)), Child(nvarchar(4000)).

In the database both dictionaries are serialized as {}, the name property of the child serializes properly. The Dictionary inside ChildThing is deserialized as empty dictionary which is to be expected considering the wrong serialization. Bizarrely the Dictionary that is directly inside Thing is deserialized as a dictionary with 1 element where the Key is "ThingId" and the value is whatever the id from the database is. Removing the Data or the Child property from Thing doesn't seem to affect the behavior of the other property.

The console output is

{"ThingId":0,"Child":{"Name":"Foo","Data":{"test":"2"}},"Data":{"test":"1"}}
Thing: 1,
Thing.Data:ThingId:1
Child.Name: Foo, Child.Data.Count: 0

EF Core version: 7
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0

[ajcvickers]

Note from triage: validate that mapped property bags contain mapped properties, other than just auto-generated key properties, excluding join tables. This likely needs to be done for both Cosmos and relational, and for both JSON and table mapping.

[michaldivis]

I encountered this too. In addition to the problem mentioned above I'm also getting an exception when trying to read the column (described in #32431)

[roji]

Is this a duplicate of #29825?