EF6 codefirst + MySql migrations: type nvarchar(max) not supported
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)
ie 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?
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...
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...
The following steps resulted in the same error message for me:
string
properties without MaxLength
in the model classes, which are mapped to nvarchar(max)
. 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, ie tables being created, but not all.
The solution for me was:
Enable-Migrations -Force
Add-Migration Initial -Force
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
.
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.
链接地址: http://www.djcxy.com/p/72714.html