Specifying ToJson().HasColumnType("json") make querying throw an exception

[Mandrakia]

EntityFramework.Core : 9.0.2, Npgsql.EntityFrameworkCore : 9.0.3

    public class TestEntity
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public List<Blog> Blogs { get; set; }
    }

    public class Blog
    {
        public string Title { get; set; }
        public string Content { get; set; }
    }

    public class Context : DbContext
   {
    protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TestEntity>(entity =>
            {
                entity.OwnsMany(a => a.Blogs, b =>
                {
                    b.ToJson().HasColumnType("json");
                });
            });
            base.OnModelCreating(modelBuilder);
        }
 }

Migration : Ok (has proper column type) Insertion : Ok Querying : System.InvalidOperationException: No coercion operator is defined between types 'System.Text.Json.JsonElement' and 'System.IO.MemoryStream'

When removing the HasColumnType("json"). Both insertion & querying work despite the column being "json" and not "jsonb" But migrating will change the column to jsonb.

[roji]

Confirmed. Simple repro (see below) does not work on SQL Server, so seems like an EFCore.PG issue. Issue to add support for HasColumnType() in EF is https://github.com/dotnet/efcore/issues/28452.

Runnable minimal repro

await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

_ = await context.Blogs.ToListAsync();

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=test;Password=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>().OwnsOne(b => b.Owned).ToJson().HasColumnType("json");
    }
}

public class Blog
{
    public int Id { get; set; }
    public Owned Owned { get; set; }
}

public class Owned
{
    public string OwnedThing { get; set; }
}

[roji]

I looked at this, and unfortunately it's not going to be possible to support json owned mapping before https://github.com/dotnet/efcore/issues/32192 is fixed on the EF side (even the jsonb support is quite hacky because of that). I'll tentatively put this in the 10 milestone, with the hope that it gets fixed on the EF side.

/cc @maumar

[ZzZombo]

@roji: looks like the linked EF Core issue is fixed.