Oof. What even is this title? Descriptive – sure, maybe – but also kinda long.
Ah, okay. This article explains how to map complex data types to a SQL database when using Entity Framework. This time, I’m using Fluent API to do this.
In my case, I just needed to store entities that I got from external source, based on entities coming from a dependency, into my service’s database. That means I couldn’t do anything about the type (IDictionary<string, object>) or add any attributes to the entities (which would usually be my go-to for entity-scoped customizations to how EF or serialization or works), but instead I needed to override OnModelCreating for my Database Context and a couple of directives to modelBuilder to make sure EF knows what to do with my (inherited) entities.
But before stepping in, let’s step out and take a look at the big picture. What was the problem I was solving again?
Problem
So I was building this small service that was calling an external source to read some data and needed to stash it in the database for further processing. I had just created my model with a couple of dbsets, and decided to run dotnet ef migrations add to create the initial migration. But that turned out to produce more red text than I’d have hoped:
PM> dotnet ef migrations add InitialCreate Build started... Build succeeded. System.InvalidOperationException: The property 'ItemBody.AdditionalData' could not be mapped because it is of type 'IDictionary<string, object>', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'. at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger`1 logger) at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(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) The property 'ItemBody.AdditionalData' could not be mapped because it is of type 'IDictionary<string, object>', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
What gives?
Reason
The reason was quite simple – I was creating a DbSet for an entity I got from a dependency, and that entity really did have a Property of type IDictionary<string, object>. That doesn’t map easily, because Entity Framework has no idea what to do with that kind of data – it doesn’t have a direct match in SQL types.
There are multiple options on what to do with data like this. Depending on your use case, you might want to pick one of the following options (or do something else entirely):
- Don’t use SQL Database, move to CosmosDB or other database that can handle random data as long as it can somehow be serialized and deserialized
- Ignore the entity altogether
- Configure a conversion between this data type and some compatible SQL data type like TEXT
Solution
Well – let’s take a closer look at the options we have. Let me go through each option below.
Time needed: 15 minutes
How to fix “The property could not be mapped because it is of type ‘IDictionary<string, object>’, which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the ‘[NotMapped]’ attribute or by using ‘EntityTypeBuilder.Ignore’ in ‘OnModelCreating’.” error in Entity Framework Core?
- Option 1: Change from SQL database to NoSQL (Not Only SQL) database
This is complicated enough I’m not going to give you a code sample. The viability of this option is highly dependent on your actual context – you might be in a situation where you simply can’t change!
But if you do want to move away from using SQL or Azure SQL, CosmosDB works great with Entity Framework nowadays, too. And who says you’d need to use EF in the first place?
Anyway – this is an architectural question that you might need to meditate on :) - Option 2: Ignore the property altogether
This is always a possibility. It actually says so in the error message itself:
Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
If you control your entities, you can just add [NotMapped] attribute on the property. But I don’t, so I need to go the Fluent route – using EntityTypeBuilder.Ignore in overridden OnModelCreating for my dbcontext instead.
This starts by figuring out which entity has this property. If you control the entities, that’s fairly easy (since you can just ctrl+shift+f the whole solution for the named property – in my case, entity ItemBody’s property AdditionalData) but I had to navigate classes in my dependencies instead. Eventually, I found it from a dependency’s Base (Parent) class’s Parent class.protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.Entity<Issue>().Ignore(x => x.AdditionalData);
}
My Issue type was coming in from a dependency, and the type it was inheriting from, was inheriting from another type, that in turn had this AdditionalData property.
If you know you can ignore it, you can do it like I did in my code snippet. - Option 3: Configure your own conversion with JsonSerializer
If you DO want to store the data in the problematic property – like AdditionalData in my case – you need to configure a compatible SQL Data type and a conversion to and from it. Sounds a bit complex, but often isn’t.
If you do control the entities, you can write a custom serializer in a separate class and insert it as an attribute. I didn’t, so I’m going to use Fluent API instead.
And how would I do that, then? Well, I’m mapping the data type to be of “TEXT”, as I don’t really know how many values this property should typically have. Text should give me some space to store arbitrary data.
Then I’m configuring conversions – simply using JsonSerializer (options for which I’ve instantiated with defaults before defining the conversions) to serialize the data should be enough for this particular case.
And finally, a basic comparer to tell different values uniqueness apart.
All of this will look somewhat like in the code snippet below this:protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var options = new JsonSerializerOptions(JsonSerializerDefaults.General);
modelBuilder
.Entity<Issue>()
.Property(x => x.AdditionalData)
.HasColumnName("AdditionalData")
.HasColumnType("TEXT")
.HasConversion(
v => JsonSerializer.Serialize(v, options),
s => JsonSerializer.Deserialize<IDictionary<string, object>>(s, options)!,
ValueComparer.CreateDefault(typeof(IDictionary<string, object>), true)
);
}
That’s it for today! Got questions, comments or feedback? Leave it in the comments-section below!
References
- Microsoft’s documentation on Fluent API (EF Core version – seems tough to find this one with Google, all you normally get is EF6…)
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
- CSOM suddenly throwing exceptions when trying to access list contents in SharePoint? A weird fix. - April 2, 2024
- “Predefined type ‘System.Object’ is not defined or imported” and other System namespace stuff missing in your solution? - March 26, 2024
- How to import GraphQL schema to Postman in Windows? - March 19, 2024
