Pomelo.entityframeworkcore.mysql: 2.1.0-preview1 Upgrade 'GeneratedTime.CreatedTimestamp' could not be mapped

[caleblloyd]

Working on the update to 2.1.0-preview1:

• Branch is here
• PR is https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/pull/510

Presently everything is failing with this error and I can't figure out what is missing:

[xUnit.net 00:00:03.3297454]       System.InvalidOperationException : The property 'GeneratedTime.CreatedTimestamp' could not be mapped, because it is of type 'DateTime' 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'.
[xUnit.net 00:00:03.3298187]       Stack Trace:
[xUnit.net 00:00:03.3298596]            at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.PropertyMappingValidationConvention.Apply(InternalModelBuilder modelBuilder)
[xUnit.net 00:00:03.3298985]            at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelBuilt(InternalModelBuilder modelBuilder)
[xUnit.net 00:00:03.3299433]            at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
[xUnit.net 00:00:03.3299740]            at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
[xUnit.net 00:00:03.3299972]            at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
[xUnit.net 00:00:03.3300151]            at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
[xUnit.net 00:00:03.3300331]            at lambda_method(Closure , ServiceProviderEngineScope )
[xUnit.net 00:00:03.3300568]            at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
[xUnit.net 00:00:03.3300821]            at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
[xUnit.net 00:00:03.3301011]            at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
[xUnit.net 00:00:03.3301269]            at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
[xUnit.net 00:00:03.3301454]            at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
[xUnit.net 00:00:03.3301660]            at Microsoft.EntityFrameworkCore.DbContext.EntryWithoutDetectChanges[TEntity](TEntity entity)
[xUnit.net 00:00:03.3301856]            at Microsoft.EntityFrameworkCore.DbContext.SetEntityState[TEntity](TEntity entity, EntityState entityState)
[xUnit.net 00:00:03.3302140]         /home/caleb/Projects/caleb/Pomelo.EntityFrameworkCore.Mysql/test/EFCore.MySql.FunctionalTests/Tests/Models/ExpressionTest.cs(32,0): at EFCore.MySql.FunctionalTests.Tests.Models.ExpressionTest..ctor()

Opened upstream at: https://github.com/aspnet/EntityFrameworkCore/issues/11233, received this response:

That exception means that the the type mapper (ITypeMappingSource in 2.1) is not returning a mapping for DateTime. ITypeMappingSource calls into your implementation of IRelationalTypeMapper if you haven't implemented the new service shapes directly.

I would suggest debugging to see why the type mapper is not returning a mapping on this code path.

Looking for help from someone who can step-through debug this issue and find the root cause. Otherwise I can work on it as I get time, but that could delay a 2.1.0-preview1 release as I don't have a lot of free time in the next couple of weeks

[yukozh]

/cc @ajcvickers

[Giantaxe]

Is there a specific setting in which the tests fail?

After cloning your repo and modifiying the MySQL connectionstring, running build.ps1.

• 131 tests found
• 130 Passed successfully
• 1 test skipped

Edit:
MySQL: 5.7.20-0ubuntu0.16.04.1 (Bash on Windows)
Visual Studio: 15.6.1 Preview 1.0
Dotnet: 2.1.300-preview1-008174

Test run for D:\Pomelo21\Pomelo.EntityFrameworkCore.MySql\test\EFCore.MySql.Tests\bin\Release\netcoreapp2.0\EFCore.MySql.Tests.dll(.NETCoreApp,Version=v2.0)
Microsoft (R) Test Execution Command Line Tool Version 15.6.0-preview-20180109-01
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
[xUnit.net 00:00:00.4560576]   Discovering: EFCore.MySql.Tests
[xUnit.net 00:00:00.5478955]   Discovered:  EFCore.MySql.Tests
[xUnit.net 00:00:00.5532887]   Starting:    EFCore.MySql.Tests
[xUnit.net 00:00:01.0968635]     EFCore.MySql.Tests.Migrations.MigrationSqlGeneratorTest.CreateIndexOperation_method [SKIP]
[xUnit.net 00:00:01.0972087]       true
Skipped  EFCore.MySql.Tests.Migrations.MigrationSqlGeneratorTest.CreateIndexOperation_method
Standard Output Messages:
 true
[xUnit.net 00:00:01.1378487]   Finished:    EFCore.MySql.Tests

Total tests: 94. Passed: 93. Failed: 0. Skipped: 1.
Test Run Successful.
Test execution time: 1,7101 Seconds
Building Migrations
Dropping database 'pomelo_test'.
Database 'pomelo_test' did not exist, no action was taken.
Done. To undo this action, use 'ef migrations remove'
Done.
Test applying migrations
EnsureCreate creates database... OK
EnsureCreate existing database... OK
EnsureDelete deletes database... OK
EnsureCreate non-existant database... OK
Migrate non-existant database... OK
Create blank database...OK
Migrate blank database... OK
All Tests Passed
Test with EF_BATCH_SIZE=1
Build started, please wait...
Build completed.

Test run for D:\Pomelo21\Pomelo.EntityFrameworkCore.MySql\test\EFCore.MySql.FunctionalTests\bin\Release\netcoreapp2.0\EFCore.MySql.FunctionalTests.dll(.NETCoreApp,Version=v2.0)
Microsoft (R) Test Execution Command Line Tool Version 15.6.0-preview-20180109-01
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
[xUnit.net 00:00:00.9953246]   Discovering: EFCore.MySql.FunctionalTests
[xUnit.net 00:00:01.0837339]   Discovered:  EFCore.MySql.FunctionalTests
[xUnit.net 00:00:01.0896624]   Starting:    EFCore.MySql.FunctionalTests
[xUnit.net 00:00:03.6460114]   Finished:    EFCore.MySql.FunctionalTests

Total tests: 37. Passed: 37. Failed: 0. Skipped: 0.
Test Run Successful.
Test execution time: 4,2181 Seconds
Test with EF_BATCH_SIZE=10
Build started, please wait...
Build completed.

Test run for D:\Pomelo21\Pomelo.EntityFrameworkCore.MySql\test\EFCore.MySql.FunctionalTests\bin\Release\netcoreapp2.0\EFCore.MySql.FunctionalTests.dll(.NETCoreApp,Version=v2.0)
Microsoft (R) Test Execution Command Line Tool Version 15.6.0-preview-20180109-01
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
[xUnit.net 00:00:00.6844280]   Discovering: EFCore.MySql.FunctionalTests
[xUnit.net 00:00:00.7676610]   Discovered:  EFCore.MySql.FunctionalTests
[xUnit.net 00:00:00.7732921]   Starting:    EFCore.MySql.FunctionalTests
[xUnit.net 00:00:03.2238353]   Finished:    EFCore.MySql.FunctionalTests

Total tests: 37. Passed: 37. Failed: 0. Skipped: 0.
Test Run Successful.
Test execution time: 3,6972 Seconds

[caleblloyd]

@Giantaxe make sure you are on ~my fork and~ the correct branch:
UPDATE: I moved my fork to this repo

git checkout f_2.1.0-preview

You should see a bunch of errors when you run the functional tests, it will look like this output from CI

UPDATE: If anyone makes fixes, PR them back to the f_2.1.0-preview in this repo

[ajcvickers]

From initial investigation it looks like this is because the "timestamp" type mappings specify their ClrType as DateTimeOffset, but the there is an attempt made to use these on properties of type DateTime. For example: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/blob/master/test/EFCore.MySql.FunctionalTests/Models/GeneratedTypes.cs#L92

[caleblloyd]

@ajcvickers both DateTime and DateTimeOffset CLR Types used to work just fine when mapped to a MySQL DATETIME or TIMESTAMP column type.

My main issue is this - what new logic is deciding that these types cannot be mapped? The stack trace is not helpful. Also I added logging to the MySqlTypeMapper on a test branch

The logging output says:

MySqlTypeMapper: FindMapping(Type clrType); clrType = System.Int32; mapping = Microsoft.EntityFrameworkCore.Storage.IntTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = Id; mapping = Microsoft.EntityFrameworkCore.Storage.IntTypeMapping
MySqlTypeMapper: FindMapping(string storeType); storeType = DATETIME; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = CreatedDateTime; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(string storeType); storeType = DATETIME(3); mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = CreatedDateTime3; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(Type clrType); clrType = System.DateTime; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = CreatedDateTime6; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = Name; mapping = EFCore.MySql.Storage.Internal.MySqlStringTypeMapping
MySqlTypeMapper: FindMapping(string storeType); storeType = DATETIME; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = UpdatedDateTime; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(string storeType); storeType = DATETIME(3); mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = UpdatedDateTime3; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(Type clrType); clrType = System.DateTime; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = UpdatedDateTime6; mapping = EFCore.MySql.Storage.Internal.MySqlDateTimeTypeMapping
System.InvalidOperationException: The property 'GeneratedTime.CreatedTimestamp' could not be mapped, because it is of type 'DateTime' 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.Metadata.Conventions.Internal.PropertyMappingValidationConvention.Apply(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelBuilt(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)

None of the FindMapping methods are returning a null.

Even if I change the DateTime columns to DateTimeOffset, I get a similar error for our JsonObject<> type:

MySqlTypeMapper: FindMapping(IProperty property); property.Name = Name; mapping = EFCore.MySql.Storage.Internal.MySqlStringTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = Value; mapping = EFCore.MySql.Storage.Internal.MySqlStringTypeMapping
MySqlTypeMapper: FindMapping(IProperty property); property.Name = Json; mapping = EFCore.MySql.Storage.Internal.MySqlStringTypeMapping
System.InvalidOperationException: The property 'JsonObject<Dictionary<string, string>>.Object' could not be mapped, because it is of type 'Dictionary<string, string>' 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.Metadata.Conventions.Internal.PropertyMappingValidationConvention.Apply(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelBuilt(InternalModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()

Again, the TypeMapper sends a type, no nulls, but still get the error.

Where does this new logic that we need to implement to "help" EF find the right mapping need to go? Thanks.

[ajcvickers]

@caleblloyd It has always been the case that the CLR type of the type mapping must match the CLR type of the property for which it is used. It may have been the case in the past that it didn't immediately generate an error, as opposed to when using it in certain ways.

I can see that it might be worthwhile for EF Core to do a check and throw an exception indicating a problem with the provider here. We tend to be generally less eager to add a lot of defensive code around providers since it adds overhead and is probably not very useful to customers--only provider writers. We also hope that providers will have significant testing--such as by running the tests that we publish, which I am pretty sure would have shown up the error when using earlier versions.

Looks like the reason it will also happen for JsonObject is because again the CLR type does not match that of the property--it's an open generic in the type mapping class.

[caleblloyd]

We also hope that providers will have significant testing--such as by running the tests that we publish, which I am pretty sure would have shown up the error when using earlier versions.

I agree but unfortunately we don't have enough contributors with enough time to do that right now :frowning:

This is probably another conversation all together but what are the chances that the Microsoft EF Core team could implement a MySQL provider? I see the number of Microsoft supported providers has recently grown with Oracle and others, with the rapid pace of innovation it would be nice if the EF team could pull in MySQL.

Looks like the reason it will also happen for JsonObject is because again the CLR type does not match that of the property--it's an open generic in the type mapping class.

Is it possible to map an open generic in 2.1.0-preview1 then?

[mguinness]

I think that's unlikely to happen as the Oracle provider is reference provider, but maybe some EF team members could help contribute to this project?

In the meanwhile, the EF team has produced a sample EF Core provider for Oracle databases. The purpose of the project is not to produce an EF Core provider owned by Microsoft, but to help us identify gaps in EF Core's relational and base functionality which we need to address in order to better support Oracle, and to jumpstart the development of other Oracle providers for EF Core by either Oracle or third parties.

[caleblloyd]

MySQL is the most popular database in StackOverflow's 2018 survey

Keeping track of EF Upstream changes, implementing all EF Upstream tests, etc is more than just the occasional contributor can keep up with. It would be a very involved job to know what is about to change and being able to provide input while changes are still in design phases.

Right now we don't have a full time contributor who is doing this. Typical cycle is we just wait for EF Upstream to publish a new version, do a diff against SQL Server, and try to move a bunch of stuff around to match.

Thought I'd try to solicit the feedback of @ajcvickers and the EF Core team here. There are a couple solutions that would lead to a better MySQL provider:

1. Call for Contributors to find more people who can help improve code quality and tests of Pomelo.EntityFrameworkCore.MySql
2. See if Microsoft EF Core team would be able to maintain a MySQL Provider (since they are driving design changes anyways) and the community could contribute to that

[mguinness]

Thanks Caleb. You've done fantastic work with Yuko to make this provider a reality. I agree that the pace of EF upstream changes is hard to keep up with and that another provider Sapient Guardian is no longer maintained. So kudos to you for all the hard work you put in to upgrade versions. So hopefully someone from the EF Core team or others can pitch in and help, as like you said MySQL is extremely popular.

[ajcvickers]

@caleblloyd Would it be helpful if we got the specification tests running in your repo?

[ajcvickers]

@roji @ErikEJ Any other ideas to help here?

[caleblloyd]

Would it be helpful if we got the specification tests running in your repo?

Yes I think that would be very helpful. I have a feeling a lot of them would fail but it would be a good starting point to know what needs to be fixed.

[ajcvickers]

@caleblloyd Filed https://github.com/aspnet/EntityFrameworkCore/issues/11305