EF6 codefirst + MySql migrations: type nvarchar(max) not supported

Question

I'm trying to use EF6 code first + MySql with migrations, but I'm stuck with this error during add-migration:

The underlying provider does not support the type 'nvarchar(max)'

It thus seems EF is trying to use SQLServer types for MySql, even if I have changed the default connection factory in my config.

I can find a lot of issues with the same error message but none of them seems up to date or to suggest something I did not already try. Here are my steps for enabling migrations in a DAL dll for MySql, could anyone give a hint?

(1) Add the NuGet packages:

• MySql.ConnectorNET.Data
• MySql.ConnectorNET.Entity

(2) Add a connection string for MySql in the App.config file, like:

(connectionStrings)
(add name="MySqlConn" 
     connectionString="Data Source=127.0.0.1;Port=3306;Database=dummy;User id=***;Password=***;charset=utf8" 
     providerName="MySql.Data.MySqlClient" /)
(/connectionStrings)

(3) Also, ensure that the configuration (in my case App.config) has been modified by NuGet as specified:

• replace the default connection factory:

  (defaultconnectionfactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" /)

• add the MySql provider:

  (provider invariantname="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /)

• add provider to system.data:

  (system.data) (dbproviderfactories) (remove name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" /) (add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /) (/dbproviderfactories) (/system.data)

(4) Manually add the code configuration type attribute (I prefer not to touch my data context code, as it must be kept generic), as recommended at http://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html:

(entityframework codeconfigurationtype="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6")

A couple of string properties in my POCO objects have no max length as they must be nvarchar(max) i.e. text in MySql. Yet, I get the error quoted above, with this stacktrace:

System.ArgumentException: The underlying provider does not support the type 'nvarchar(max)'.
   at MySql.Data.MySqlClient.MySqlProviderManifest.GetEdmType(TypeUsage storeType)
   at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildColumnModel(EdmProperty property, ModelMetadata modelMetadata, IDictionary`2 annotations)
   at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildAlterColumnOperation(String table, EdmProperty targetProperty, ModelMetadata targetModelMetadata, EdmProperty sourceProperty, ModelMetadata sourceModelMetadata)
   at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.(FindAlteredColumns)b__24b(()f__AnonymousType2c`2 ()h__TransparentIdentifier243)
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(ModelMetadata source, ModelMetadata target, Lazy`1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator, String sourceModelVersion, String targetModelVersion)
   at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(XDocument sourceModel, XDocument targetModel, Lazy`1 modificationCommandTreeGenerator, MigrationSqlGenerator migrationSqlGenerator, String sourceModelVersion, String targetModelVersion)
   at System.Data.Entity.Migrations.DbMigrator.Scaffold(String migrationName, String namespace, Boolean ignoreChanges)
   at System.Data.Entity.Migrations.Design.MigrationScaffolder.Scaffold(String migrationName, Boolean ignoreChanges)
   at System.Data.Entity.Migrations.Design.ToolingFacade.ScaffoldRunner.Scaffold(MigrationScaffolder scaffolder)
   at System.Data.Entity.Migrations.Design.ToolingFacade.ScaffoldRunner.Run()
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner)
   at System.Data.Entity.Migrations.Design.ToolingFacade.Scaffold(String migrationName, String language, String rootNamespace, Boolean ignoreChanges)
   at System.Data.Entity.Migrations.AddMigrationCommand.Execute(String name, Boolean force, Boolean ignoreChanges)
   at System.Data.Entity.Migrations.AddMigrationCommand.()c__DisplayClass2.(.ctor)b__0()
   at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command)
The underlying provider does not support the type 'nvarchar(max)'.

Any hint?

Answer 1

I struggled with this same error all night. The fix was counter-intuitive but so far so good... It does seem that the issue had to do with old migrations that were set up with LocalDB (SQL Server) yet even deleting the old migrations had no effect. I also spent lots of unnecessary time fiddling with my Web.config, Database Context, etc... I suggest trying the following before banging your head on the wall...

1. Delete the migrations folder
2. Try enabling migrations and adding your initial migration (in the PM type "enable-migrations" and then "add-migration init")
3. If you get the above error in the PM, delete the migrations folder again, then open up MySQL Workbench
4. Within MySQL Workbench, locate the Schema with the name you gave in your connectionString (will look like database=whateverYouCalledIt)
5. In that schema, open the tables and find "__migrationhistory"
6. Right click that table, click select rows, and delete any existing migration entries, then repeat step 2

For some reason this did the trick for me when deleting migrations in the solution explorer had no effect. Apparently, that didn't delete the migration history in the database...