Sharing JSON column between different generic implementations in TPH inheritance

[rafalkwol]

Hello,

Since I heard about the JSON columns in EF Core 7 I was very excited about this feature. I was using JSON as a part of out data structure in SQL Server for quite a while and using it with EF Core was quite cumbersome.

Today I wanted to try it out to check if my use case could benefit from this new feature and so far I can't figure out if it's even an option for me.

So the use case is this. I want to have a table with TPH inheritance. This table will keep records of different messages sent between multiple systems. All the messages have a defined structure so each message will have it's own C# class. I want to keep all the messages in the same table and differentiate them by the discriminator of the record and also be able to query each of them by some of their properties, modify them etc.

Below is the code I tried to use to prototype this:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContextPool<JsonColumnDatabaseContext>((x, y) =>
{
    y.UseSqlServer("");
});

var app = builder.Build();

app.MapGet("/", () => "Hello World!");

app.Run();

public class JsonColumnDatabaseContext : DbContext
{
    public JsonColumnDatabaseContext(DbContextOptions<JsonColumnDatabaseContext> dbContextOptions)
        : base(dbContextOptions)
    {

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.ApplyConfigurationsFromAssembly(GetType().Assembly);
    }
}

public class JsonEntity
{
    public int Id { get; set; }
}

public class JsonEntity<TMessage> : JsonEntity
    where TMessage : class
{
    public TMessage Message { get; set; } = default!;
}

public class CustomerMessage
{
    public string? Name { get; set; }
    public string? Code { get; set; }
}

public class DocumentMessage
{
    public int? DocumentId { get; set; }
}

public class JsonEntityConfiguration : IEntityTypeConfiguration<JsonEntity>
{
    public void Configure(EntityTypeBuilder<JsonEntity> builder)
    {
        builder.HasDiscriminator<string>("MessageType");
    }
}

public abstract class JsonEntityMessageConfiguration<TMessage> : IEntityTypeConfiguration<JsonEntity<TMessage>>
    where TMessage : class
{
    public void Configure(EntityTypeBuilder<JsonEntity<TMessage>> builder)
    {
        builder.OwnsOne(x => x.Message, x => { x.ToJson(); });
        builder.HasBaseType<JsonEntity>().HasDiscriminator<string>("MessageType");
    }
}

public class JsonEntityCustomerMessageConfiguration : JsonEntityMessageConfiguration<CustomerMessage>
{
}

public class JsonEntityDocumentMessageConfiguration : JsonEntityMessageConfiguration<DocumentMessage>
{
}

When I try to create a migration for the above code I get a stack trace like this:

System.InvalidOperationException: Multiple owned root entities are mapped to the same JSON column 'Message' in table 'JsonEntity'. Each owned root entity must map to a different column.
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.ValidateJsonEntities(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal.SqlServerModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__8_4(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass2_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_ContextServices()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Multiple owned root entities are mapped to the same JSON column 'Message' in table 'JsonEntity'. Each owned root entity must map to a different column.

From the information above the thing that comes to my mind is that it's not possible to have a generic base class with a specific property that is treated as a JSON column that have a different type per implementation. Is this true or am I just missing something? Or is there any other way to achive this than the one described?

I know that when using inheritance only the properties with the same type in the derived classes can have the same column mapped to them. I was hoping as JSON columns basically are translated into the same type on the database side this kind of structure would be possible. I would be grateful if someone more in the know could shed some light on this issue.

EF Core version: 7.0.0-rc.2.22472.11
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11 Professional
IDE: Visual Studio Code 1.72.2

[ajcvickers]

@rafalkwol Can you show an example of the data in your table?

[rafalkwol]

@ajcvickers Let's say it looks something like this in a very simplified form. In reality JSON in "Message" column can be basically anything that can be represented as C# model. Each C# class have different "MessageType" so we can be sure that the "Message" is always structured the same way for each discriminator.

[OskarKlintrot]

I know that when using inheritance only the properties with the same type in the derived classes can have the same column mapped to them. I was hoping as JSON columns basically are translated into the same type on the database side this kind of structure would be possible.

I was hoping for this as well since they both translates to string. My use case is that I want to have an inbox for multiple events mapped to the same table. Instead of storing the event a string in the table it would be helpful to be able to map them directly to their respective dto's.

Here's my sample where I bumped into this; https://github.com/OskarKlintrot/JsonColumn

[AndriySvyryd]

You should be able to map this by using JSON column sharing once it's implemented

[rafalkwol]

@AndriySvyryd Any information on the time it's planned as a feature? I'm guessing not earlier than .NET 8?

[AndriySvyryd]

It's not currently planned for any release as it doesn't have any votes (👍 )

[roji]

@rafalkwol note also that .NET/EF 7.0 will be released next week, so definitely no new features are being added to it until then. The next release is EF 8.0 - in a year - which is the earliest where this could be implemented (excluding previews).

[rafalkwol]

@roji Oh, that's fine. I was just hoping I could make it work somehow right now, but if it's not possible for now than I will just figure this out some other way. Anyway I like the direction where EF Core is going with all the new features being added and it's really becoming better and better with every release in my opinion. I hope the innovation will continue and we will get something even more interesting next time.

[ajcvickers]

Duplicate of #28592.