efcore Can't map Dictionary<string,object> (e.g. to JSON) since it's detected as a property bag

With (PostgreSQL) native JSON support, it's useful to map Dictionary directly to JSON columns. This works e.g. for Dictionary<string,string>, but fails for Dictionary<string,object> since the property is detected as a property bag:

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

    [Column(TypeName = "jsonb")]
    public Dictionary<string, object> JsonProperty { get; set; }
}

The exception:

The navigation 'Blog.JsonProperty' must be configured in 'OnModelCreating' with an explicit name for the target shared-type entity type, or excluded by calling 'EntityTypeBuilder.Ignore'.

We may want to stop detecting property bags if column has an explicit store type. Following on how type converters work, this can be worked around by configuring the property with .Metadata.SetProviderClrType(null).

/cc @AndriySvyryd

Originally filed by @ColinZeb in https://github.com/npgsql/efcore.pg/issues/2134

Dec 06 '21 10:12 roji

Note from triage: @roji to give this a try on current main.

Dec 09 '21 15:12 ajcvickers

Confirmed that the exception still occurs on latest main (590c78397230c95c64e4d4d15a1f522c66936464):

Unhandled exception. System.InvalidOperationException: The navigation 'Blog.JsonProperty' must be configured in 'OnModelCreating' with an explicit name for the target shared-type entity type, or excluded by calling 'EntityTypeBuilder.Ignore'.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger) in /home/roji/projects/efcore/src/EFCore/Infrastructure/ModelValidator.cs:line 244
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger) in /home/roji/projects/efcore/src/EFCore/Infrastructure/ModelValidator.cs:line 48
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger) in /home/roji/projects/efcore/src/EFCore.Relational/Infrastructure/RelationalModelValidator.cs:line 49
   at Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure.NpgsqlModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger) in /home/roji/projects/efcore.pg/src/EFCore.PG/Infrastructure/NpgsqlModelValidator.cs:line 27
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger) in /home/roji/projects/efcore/src/EFCore/Infrastructure/ModelRuntimeInitializer.cs:line 84
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime) in /home/roji/projects/efcore/src/EFCore/Infrastructure/ModelSource.cs:line 72
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime) in /home/roji/projects/efcore/src/EFCore/Internal/DbContextServices.cs:line 86
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model() in /home/roji/projects/efcore/src/EFCore/Internal/DbContextServices.cs:line 113
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__8_4(IServiceProvider p) in /home/roji/projects/efcore/src/EFCore/Infrastructure/EntityFrameworkServicesBuilder.cs:line 276
   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() in /home/roji/projects/efcore/src/EFCore/DbContext.cs:line 430
   at Microsoft.EntityFrameworkCore.DbContext.get_ContextServices() in /home/roji/projects/efcore/src/EFCore/DbContext.cs:line 412
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider() in /home/roji/projects/efcore/src/EFCore/DbContext.cs:line 366
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance() in /home/roji/projects/efcore/src/EFCore/DbContext.cs:line 2115
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor) in /home/roji/projects/efcore/src/EFCore/Infrastructure/Internal/InfrastructureExtensions.cs:line 25
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor) in /home/roji/projects/efcore/src/EFCore/Infrastructure/AccessorExtensions.cs:line 42
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies() in /home/roji/projects/efcore/src/EFCore/Infrastructure/DatabaseFacade.cs:line 31
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureDeletedAsync(CancellationToken cancellationToken) in /home/roji/projects/efcore/src/EFCore/Infrastructure/DatabaseFacade.cs:line 193
   at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 11
   at Program.<Main>$(String[] args) in /home/roji/projects/test/Program.cs:line 12
   at Program.<Main>(String[] args)

Dec 10 '21 10:12 roji

@roji By all means let me know if this comment is misplaced.

I've encountered the same issue with mssql using the new JSON columns functionality.

public class Product
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Sku { get; set; }
    public decimal Price { get; set; }
    public CustomFields CustomFields { get; set; }
}

public class CustomFields 
{
    public Dictionary<string, object> Fields { get; set; }
}

public class Context : DbContext
{
    private bool _isConfigured;
    public Context() { }
    public Context(DbContextOptions<Context> options) : base(options) 
        => _isConfigured = true;

    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().OwnsOne(p => p.CustomFields, ownedNavigationBuilder =>
        {
            ownedNavigationBuilder.ToJson();
        });
    }
}

The navigation 'CustomFields.Fields' must be configured in 'OnModelCreating' with an explicit name for the target shared-type entity type, or excluded by calling 'EntityTypeBuilder.Ignore

Using the workaround suggested in https://github.com/npgsql/efcore.pg/issues/2134#issuecomment-986656051 by altering the OnModelCreating to the following:

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().OwnsOne(p => p.CustomFields, ownedNavigationBuilder =>
        {
            ownedNavigationBuilder.ToJson();
            ownedNavigationBuilder.Property(c => c.Fields).Metadata.SetProviderClrType(null);
        });
    }

Gives the following error:

The 'Dictionary<string, object>' property 'CustomFields.Fields' could not be mapped because the database provider does not support this type. Consider converting the property value to a type supported by the database using a value converter. See https://aka.ms/efcore-docs-value-converters for more information. Alternately, exclude the property from the model using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'

I could get around this using a custom ValueConverter for CustomFields.Fields - but am I missing something with the ToJson functionality here?

Thanks in advance - and again let me know if this is misplaced.

Nov 28 '22 11:11 Mfolmer

@Mfolmer you're definitely not supposed to be able to map a Dictionary<string, object> via ToJson. At this point, ToJson is only for mapping strongly-typed CLR-based entity types. We have plans to improve on that (#28871), though support is likely to take the form of JsonDocument/JsonElement support rather than via Dictionary.

Nov 28 '22 12:11 roji

@roji Alright - thanks for your quick respons! :+1:

Nov 28 '22 15:11 Mfolmer

How can we map Json column(nvarchar(max)) in mssql to Dictionary<int, string>? Json looks like: {"100": "value", "101": "value" }

modelBuilder.Entity<JsonTableEntity>().OwnsOne(x => x.Json, builder => { builder.ToJson(); })

Sql, which generated by ef core looked like code below, but dictionary value in result objects is null SELECT JSON_QUERY([p].[Json],'$') FROM [JsonTable] AS [p]

Dec 10 '22 21:12 SomePrettyUsername

@SomePrettyUsername this isn't currently supported - you can currently only map a strongly-typed CLR type to JSON (see our docs). This issue specifically isn't related to ToJson(), I've opened #29825 to track this.

Dec 11 '22 11:12 roji

Related to https://github.com/dotnet/efcore/issues/28871

Apr 22 '23 00:04 AndriySvyryd

With (PostgreSQL) native JSON support, it's useful to map Dictionary directly to JSON columns. This works e.g. for Dictionary<string,string>, but fails for Dictionary<string,object> since the property is detected as a property bag:
public class Blog
{
    public int Id { get; set; }

    [Column(TypeName = "jsonb")]
    public Dictionary<string, object> JsonProperty { get; set; }
}
The exception:
The navigation 'Blog.JsonProperty' must be configured in 'OnModelCreating' with an explicit name for the target shared-type entity type, or excluded by calling 'EntityTypeBuilder.Ignore'.
We may want to stop detecting property bags if column has an explicit store type. Following on how type converters work, this can be worked around by configuring the property with .Metadata.SetProviderClrType(null).

/cc @AndriySvyryd

Originally filed by @ColinZeb in npgsql/efcore.pg#2134 This problem can be solved in the following way： [NotMapped] public Dictionary<string, object> Contents { get { return string.IsNullOrWhiteSpace(ContentsJson) ? new Dictionary<string, object>() : System.Text.Json.JsonSerializer.Deserialize<Dictionary<string, object>>(ContentsJson); } set { ContentsJson = JsonSerializer.Serialize(value, Extention.jsonSerializerOptions); } } [JsonIgnore] public string? ContentsJson { get; set; }

Oct 31 '23 03:10 Dean-ZhenYao-Wang

Any chance this is coming in EF core 9?

May 21 '24 19:05 onionhammer

This issue is in the Backlog milestone. This means that it is not planned for the next release. We will re-assess the backlog following this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources, see Release planning process. Make sure to vote (👍) for this issue if it is important to you.

May 21 '24 22:05 AndriySvyryd