Entity Framework 6 with SQL Server connection tries to use MySqlClient and crashes

Question

I have an application that uses EF6. It supports both SQL Server and MySql, and the appropriate settings are applied at runtime (using MySqlEFConfiguration if it's set to MySql, etc).

With MySql everything works flawlessly.

With SQL Server, the following happens:

1. The connection is established successfully.
2. Calling context.Database.Initialize() results in all the normal queries for the latest MigrationId etc, and if the db/tables don't exist, it creates them successfully.
3. Then, it crashes with the following exception:

Schema specified is not valid.
Errors:
(0,0) : error 0175: The ADO.NET provider with invariant name 'MySql.Data.MySqlClient' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details.

It says see inner exception for details, but the inner exception is null.

I have searched everywhere, tried fiddling endlessly with the config file (the provider definitions etc) and every time it's the same.

I have tried to reproduce the problem in a clean project and did not succeed, it always works as expected.

Why is it trying to use MySqlClient?

Edit:

The config code is nothing special, just a

            if (Settings.Default.databaseType == "MySql")
            {
                DbConfiguration.SetConfiguration(new MySqlEFConfiguration());
            }

I have verified that this is not called when using SQL Server. The connection strings are also set at runtime and I have verified those too. All the config code was copied to the clean project in the attempt to reproduce the problem, but I haven't managed to trigger it.

The relevant config sections:

  <system.data>
    <DbProviderFactories>
      <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.EntityFramework" />
    </providers>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
  <connectionStrings>
    <clear />
    <add name="mydbcontext" connectionString="" providerName="" />
  </connectionStrings>

Edit 2: here's the call stack in case it's any help. As far as I can tell, for some reason DefaultProviderFactoryResolver gives the wrong answer. But why? Using a custom ProviderFactoryResolver has no effect.

    EntityFramework.dll!<>c.AnonymousMethod(System.ArgumentException e = {unknown}, string n = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(System.Type type = {unknown}, object key = {unknown}, System.Func<System.ArgumentException,string,object> handleFailedLookup = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.DefaultProviderFactoryResolver.GetService(System.Type type = {unknown}, object key = {unknown})  C#  Non-user code
    EntityFramework.dll!<>c__DisplayClass4_0.AnonymousMethod(System.Tuple<System.Type,object> k = {unknown})    C#  Non-user code
    mscorlib.dll!System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key = {unknown}, System.Func valueFactory = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.CachingDependencyResolver.GetService(System.Type type = {unknown}, object key = {unknown})   C#  Non-user code
    EntityFramework.dll!<>c__DisplayClass5_0.AnonymousMethod(System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver r = {unknown})    C#  Non-user code
    System.Core.dll!WhereSelectArrayIterator`2.MoveNext()   C#  Non-user code
    System.Core.dll!System.Linq.Enumerable.FirstOrDefault(System.Collections.Generic.IEnumerable source = {unknown}, System.Func predicate = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(System.Type type = {unknown}, object key = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.RootDependencyResolver.GetService(System.Type type = {unknown}, object key = {unknown})  C#  Non-user code
    EntityFramework.dll!<>c__DisplayClass5_0.AnonymousMethod(System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver r = {unknown})    C#  Non-user code
    System.Core.dll!WhereSelectArrayIterator`2.MoveNext()   C#  Non-user code
    System.Core.dll!System.Linq.Enumerable.FirstOrDefault(System.Collections.Generic.IEnumerable source = {unknown}, System.Func predicate = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(System.Type type = {unknown}, object key = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(System.Type type = {unknown}, object key = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService(System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver resolver = {unknown}, object key = {unknown}) C#  Non-user code
    EntityFramework.dll!Loader.InitializeProviderManifest(System.Action<string,System.Data.Entity.Core.SchemaObjectModel.ErrorCode,System.Data.Entity.Core.Metadata.Edm.EdmSchemaErrorSeverity> addError = {unknown})   C#  Non-user code
    EntityFramework.dll!Loader.OnProviderManifestTokenNotification(string token = {unknown}, System.Action<string,System.Data.Entity.Core.SchemaObjectModel.ErrorCode,System.Data.Entity.Core.Metadata.Edm.EdmSchemaErrorSeverity> addError = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.Schema.HandleProviderManifestTokenAttribute(System.Xml.XmlReader reader = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.Schema.HandleAttribute(System.Xml.XmlReader reader = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.SchemaElement.ParseAttribute(System.Xml.XmlReader reader = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.SchemaElement.Parse(System.Xml.XmlReader reader = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.Schema.HandleTopLevelSchemaElement(System.Xml.XmlReader reader = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.Schema.InternalParse(System.Xml.XmlReader sourceReader = {unknown}, string sourceLocation = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.Schema.Parse(System.Xml.XmlReader sourceReader = {unknown}, string sourceLocation = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.SchemaObjectModel.SchemaManager.ParseAndValidate(System.Collections.Generic.IEnumerable<System.Xml.XmlReader> xmlReaders = {unknown}, System.Collections.Generic.IEnumerable<string> sourceFilePaths = {unknown}, System.Data.Entity.Core.SchemaObjectModel.SchemaDataModelOption dataModel = {unknown}, System.Data.Entity.Core.SchemaObjectModel.AttributeValueNotification providerNotification = {unknown}, System.Data.Entity.Core.SchemaObjectModel.AttributeValueNotification providerManifestTokenNotification = {unknown}, System.Data.Entity.Core.SchemaObjectModel.ProviderManifestNeeded providerManifestNeeded = {unknown}, out System.Collections.Generic.IList<System.Data.Entity.Core.SchemaObjectModel.Schema> schemaCollection = {unknown})   C#  Non-user code
    EntityFramework.dll!Loader.LoadItems(System.Collections.Generic.IEnumerable<System.Xml.XmlReader> xmlReaders = {unknown}, System.Collections.Generic.IEnumerable<string> sourceFilePaths = {unknown})   C#  Non-user code
    EntityFramework.dll!Loader..ctor(System.Collections.Generic.IEnumerable<System.Xml.XmlReader> xmlReaders = {unknown}, System.Collections.Generic.IEnumerable<string> sourceFilePaths = {unknown}, bool throwOnError = {unknown}, System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver resolver = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.Metadata.Edm.StoreItemCollection.Init(System.Collections.Generic.IEnumerable<System.Xml.XmlReader> xmlReaders = {unknown}, System.Collections.Generic.IEnumerable<string> filePaths = {unknown}, bool throwOnError = {unknown}, System.Data.Entity.Infrastructure.DependencyResolution.IDbDependencyResolver resolver = {unknown}, out System.Data.Entity.Core.Common.DbProviderManifest providerManifest = {unknown}, out System.Data.Common.DbProviderFactory providerFactory = {unknown}, out string providerInvariantName = {unknown}, out string providerManifestToken = {unknown}, out System.Data.Entity.Core.Common.Utils.Memoizer<System.Data.Entity.Core.Metadata.Edm.EdmFunction,System.Data.Entity.Core.Metadata.Edm.EdmFunction> cachedCTypeFunction = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Core.Metadata.Edm.StoreItemCollection..ctor(System.Collections.Generic.IEnumerable<System.Xml.XmlReader> xmlReaders = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Utilities.XDocumentExtensions.GetStorageMappingItemCollection(System.Xml.Linq.XDocument model = {unknown}, out System.Data.Entity.Infrastructure.DbProviderInfo providerInfo = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.Diff(System.Xml.Linq.XDocument sourceModel = {unknown}, System.Xml.Linq.XDocument targetModel = {unknown}, System.Lazy<System.Data.Entity.Migrations.Infrastructure.ModificationCommandTreeGenerator> modificationCommandTreeGenerator = {unknown}, System.Data.Entity.Migrations.Sql.MigrationSqlGenerator migrationSqlGenerator = {unknown}, string sourceModelVersion = {unknown}, string targetModelVersion = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.IsModelOutOfDate(System.Xml.Linq.XDocument model = {unknown}, System.Data.Entity.Migrations.DbMigration lastMigration = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.ApplyMigration(System.Data.Entity.Migrations.DbMigration migration = {unknown}, System.Data.Entity.Migrations.DbMigration lastMigration = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.Upgrade(System.Collections.Generic.IEnumerable<string> pendingMigrations = {unknown}, string targetMigrationId = {unknown}, string lastMigrationId = {unknown})    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.UpdateInternal(string targetMigration = {unknown}) C#  Non-user code
    EntityFramework.dll!<>c__DisplayClass42_0.AnonymousMethod() C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(System.Action mustSucceedToKeepDatabase = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Migrations.DbMigrator.Update(string targetMigration = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.MigrateDatabaseToLatestVersion`2.InitializeDatabase(TContext context = {unknown})    C#  Non-user code
    EntityFramework.dll!<>c__DisplayClass66_0`1.AnonymousMethod()   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.InternalContext.PerformInitializationAction(System.Action action = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() C#  Non-user code
    EntityFramework.dll!<>c.AnonymousMethod(System.Data.Entity.Internal.InternalContext c = {unknown})  C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input = {unknown})   C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(System.Action<System.Data.Entity.Internal.InternalContext> action = {unknown}) C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Internal.InternalContext.Initialize()    C#  Non-user code
    EntityFramework.dll!System.Data.Entity.Database.Initialize(bool force = {unknown})  C#  Non-user code

Answer 1

Finally solved it by adding:

  <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL"
 type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data" />

to the DbProviderFactories config section. Why is this necessary when using SQL Server, but NOT necessary when using MySql? I haven't the slightest idea.

Answer 2

You need to configure it differently for SqlServer and MySql. Below is my personal implementation.

---

First I Create a new class that extends the DbContext class. Helps keep things seperate and organised

public class MySqlContext : DbContext
{
    public MySqlContext(DbContextOptions options)
     : base(options)
    {
    }
}

I then create a new Extentions class which again helps everything stay organised. It's also generic so I can add multiple contexted easily. Note below I use UseSqlServer, however, or Mysql you will need to use UseMySQL. You might need to install the package for that.

public static class ServiceCollectionExtentions
{
    /// <summary>
    /// Add SQL context
    /// </summary>
    /// <param name="services"></param>
    /// <param name="options"></param>
    /// <returns></returns>
    public static IServiceCollection AddContext<TContext>(this IServiceCollection services, string connectionstring, ServiceLifetime serviceLifetime = ServiceLifetime.Scoped) where TContext : DbContext => services
        .AddDbContext<TContext>(options => options.UseSqlServer( // You can replace this with `UseMySQL` 
                connectionstring,
                
                // With this I change where the migrations are generated
                // This is just the project I use for the data access layer
                actions => actions.MigrationsAssembly("<Your Project Namespace>")
                                  .EnableRetryOnFailure()
            ), serviceLifetime);
}

Here is an example usage:

public void ConfigureServices(IServiceCollection services)
{
    // Add the SQL db conneciton
    services.AddContext<MySqlContext>(Configuration.GetConnectionString("SqlConnection"));
}