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

8
votes

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?

3
mysqlentity-frameworkentity-framework-migrations
6 months later and I have exactly the same problem. I could have wrote this question down to the last detail. Did you by chance ever come to any answers with this? – Adam
Unfortunately I didn't. Should I find any solution (other than explicitly setting the types via attributes or fluent syntax) I'll post it here. At least I'm not alone :) – Naftis
I did find something interesting. I attempted to convert a project over from using mssql, so I already had some migrations. Normally with our error you can't even run add-migration. However, since I had the migration files already in place, I could run update-database...which RAN all of the migrations just fine, but then after adding all the schema changes, it crapped out with this error...I'm guessing it occurs when the Context is initiated for the first time before seeding. Just crazy that I was able to successfully roll forward (AND backward) via update-database. – Adam
It's like EF is using one provider for the migration code, and a different provider for the context. – Adam
Made more infuriating by the fact that I have no longtext/text/nvarchar(max)/varchar(max) fields in my data model. – Adam

3 Answers

2
votes

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...

0
votes

The following steps resulted in the same error message for me:

  1. Create a project using entity framework and SQL Server, with a migration. Use some string properties without MaxLength in the model classes, which are mapped to nvarchar(max).
  2. Reconfigure for MySQL like above, for an empty MySQL database.
  3. Run Update-Database.

Using all the latest software versions libraries at moment of writing.

After getting the error I tried setting [MaxLength(..)] on all string properties. The error message kept coming up, even at recreation of the migration with Add-Migration Initial -Force.

In my case it turned out that the call to Update-Database on my MySQL database (which ran the old migrations meant for SQL Server including nvarchar(max)) had resulted in some effects on my database, i.e. tables being created, but not all.

The solution for me was:

  1. Drop these tables, make sure the database was empty again.
  2. Enable-Migrations -Force
  3. Add-Migration Initial -Force
  4. Update-Database

Note that these steps overwrite any manual changes to your migrations and Seed() function, you might want to restore those selectively.

After this I tested string properties without [MaxLength(..)]. These can be used just fine with the MySQL provider, they are mapped to longtext.

0
votes

I had the same problem and found the root cause was existing migrations that looked like this: AddColumn("dbo.RoundJumperMap", "VideoUrl", c => c.String());

Once I found all those and added a maxlength everything ran great. AddColumn("dbo.RoundJumperMap", "VideoUrl", c => c.String(maxLength: 1000));

I did not need to delete any of my existing migrations or anything like that.