Limits mapping to JSON columns - EF Core 7, SQL Server

[christiannagel]

Trying to do some ToJson mappings, it seems I'm hitting some limitations with TpH and TpC.

With an abstract base class and derived generic types, such as

public abstract record class MoveData(Guid GameId, Guid MoveId, int MoveNumber);

public record class MoveData<TField>(Guid GameId, Guid MoveId, int MoveNumber)
    : MoveData(GameId, MoveId, MoveNumber)
{
    public required ICollection<TField> Fields { get; init; }
}

and using these types for TField:

public record ColorField(string Color);

public record ShapeAndColorField(string Shape, string Color);

And using TpH, mapping the fields with OwnsMany / ToJson, I had the error it's not possible to map different derived types to the same column. Is this because of OwnsMany restrictions? I was successful mapping to different columns.

The complete sample project with similar functionality with a conversion to a simple string instead of conversion to JSON:
https://github.com/ProfessionalCSharp/ProfessionalCSharp2021/tree/main/5_More/EFCore/InhertianceMappingWithConversion

Using TpC instead, I reached an InvalidOperationException with RelationalStrings.JsonEntityWithNonTphInheritanceOnOwner.

ToJson is not supported with TpT and TpC?

Provider and version information

EF Core version: 7.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11 22H2 22621.1344
IDE: Visual Studio 2022 17.6.0 Preview 1

[ajcvickers]

TPT/TPC mapping is tracked by #28443

Looks like the other issue is likely this: #28688

[christiannagel]

Thanks, @ajcvickers

Yes for the TPT/TPC mapping.

It doesn't look like the other issue is a match. In my scenario I don't have primitive types directly in the collection - and it works if I create separate columns for every derived class. With a converter, I can map properties of derived types to the same column. However, when using ToJson (which requires OwnsMany) this is not possible.

[ajcvickers]

@christiannagel While #28688 says primitive types, this applies to any non-entity types. Non-entity types will always have limitations if they are stored as values of types not recognized by the database, since database querying into JSON is generally limited to types the database knows about. On the other hand, just storing non-entity types should be fine once #28688, assuming the type converts appropriately to JSON.

[christiannagel]

@ajcvickers -I think this is a different issue because serialization as JSON works if I use different columns from the derived types. This could be a limitation of OwnsMany. Is it correct that I need OwnsMany to write a JSON collection?

Details (with the complete project referenced below):

In this scenario I have the MoveData base class, and MoveData<T> derived class. MoveData is declared to use the TPH mapping strategy with the concrete typed MoveData<ColorField> and MoveData<ShapeAndColorField> declared with discriminator values:

        modelBuilder.Entity<MoveData>().UseTphMappingStrategy();

        modelBuilder.Entity<MoveData>().HasDiscriminator<string>("gameType")
            .HasValue<MoveData<ColorField>>("color")
            .HasValue<MoveData<ShapeAndColorField>>("shape");

        // The JSON columns cannot map to the same column name!
        modelBuilder.ApplyConfiguration(new MoveDataConfiguration<ColorField>("Moves", "ColorFields"));
        modelBuilder.ApplyConfiguration(new MoveDataConfiguration<ShapeAndColorField>("Moves", "ShapeFields"));

If I map the JSON column from the concrete types to different column names, everything is working:

internal class MoveDataConfiguration<T> : IEntityTypeConfiguration<MoveData<T>>
    where T : class
{
    private readonly string _tableName;
    private readonly string _jsonColumnName;
    public MoveDataConfiguration(string tableName, string jsonColumnName)
    {
        _tableName = tableName;
        _jsonColumnName = jsonColumnName;
    }

    public void Configure(EntityTypeBuilder<MoveData<T>> builder)
    {
        builder.ToTable(_tableName);

        builder.HasOne<GameData>()
            .WithMany(g => (IEnumerable<MoveData<T>>)g.Moves)
            .HasForeignKey(m => m.GameId)
            .OnDelete(DeleteBehavior.Cascade);

        builder.OwnsMany(m => m.Fields, navigationBuilder =>
        {
            navigationBuilder.ToJson(_jsonColumnName);
        });
    }
}

Using the same column name, I get this exception:

System.InvalidOperationException: 'Multiple owned root entities are mapped to the same JSON column 'Fields' in table 'Moves'. Each owned root entity must map to a different column.'

The complete project is in the https://github.com/ProfessionalCSharp/ProfessionalCSharp2021/ repo - in the efcoretpcandjson branch:

https://github.com/ProfessionalCSharp/ProfessionalCSharp2021/tree/efcoretpcandjson/5_More/EFCore/InhertianceMappingTpCandJSON

[ajcvickers]

Thanks for providing more details. That is indeed a different issue, tracked by #28592.

[christiannagel]

Thank you - I really missed finding this issue.

Both TPT/TPC with JSON columns and sharing the same JSON column are "consider-for-current-release". Thanks!

https://github.com/dotnet/efcore/issues?q=is%3Aopen+is%3Aissue+label%3Aconsider-for-current-release+label%3Aarea-json