Same application, different databases: Entity framework 6.X + MySQL + SQL Server

Question

Yesterday I did migration (EF 5.0 => EF 6.0) of web application that uses entity framework to reach MySql and SQL Server databases (particular DbContext to particular databases, NOT any DbContext to any type of database).

Compile time things were done without any issues, run-time faced me with exception:

The default DbConfiguration instance was used by the Entity Framework before the 'MySqlEFConfiguration' type was discovered.

The [DbConfigurationType(typeof(MySqlEFConfiguration))] attribute on the context appears to have been ignored at run time because the context is in an external assembly(?) and the DbConfiguration used instead is global to the application domain, not the specific to the context(?)."

I tried different approaches to fix it, then googled it and - surprise - find no working solution.

Looks like situation described well formed here http://forums.mysql.com/read.php?174,614148,614148 still not changed, or I missed some obvious things.

Any feedback will be appreciated.

Thank you in advance!

DETAILED DESCRIPTION:

Input (simplified): - ASP.NET Web Application

• Data access layer implemented over Entity Framework 6.1.1

• Entity Framework providers:

  • System.Data.SqlClient 6.1.1

  • MySql.Data.MySqlClient 6.9.4

• MY_SqlContext, model first concept, targeted to MY SQL Server database

• Ms_SqlContext, database first concept, targeted to MS SQL Server database

According generic documentation of Entity Framework 6 and MySql Connector/Net documentation (http://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html), MY_SqlContext requires MySqlEFConfiguration to be applied.

According both documentations, refered above, there are three options to do that. All three was tried and failed.

Option 1: Adding the DbConfigurationTypeAttribute [DbConfigurationType(typeof(MySqlEFConfiguration))] to MY_SqlContext class

Appropriate Web.config segments:

<connectionStrings>
    <add name="MY_SqlContext"
         connectionString="server=.;User Id=root;password=...;Persist Security Info=True;database=MySqlDb;Use Compression=False;Use Old Syntax=False"
         providerName="MySql.Data.MySqlClient" />
    <add name="Ms_SqlContext"
         connectionString="metadata=res://*/Ms_SqlContext.csdl|res://*/Ms_SqlContext.ssdl|res://*/Ms_SqlContext.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=MsSqlDb;User ID=appuser;Password=...&quot;"
         providerName="System.Data.EntityClient" />
</connectionStrings>

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices,  MySql.Data.Entity.EF6" />
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

After application starts and web requests begin processing:

Ms_SqlContext works fine but trying to create a MY_SqlContext instance, I get the exception:

The default DbConfiguration instance was used by the Entity Framework before the 'MySqlEFConfiguration' type was discovered. An instance of 'MySqlEFConfiguration' must be set at application start before using any Entity Framework features or must be registered in the application's config file. See ...LinkId=260883 for more information."

Option 2: Calling DbConfiguration.SetConfiguration(new MySqlEFConfiguration()) at the application startup

Appropriate Web.config segments (same as Option 1, actually):

<connectionStrings>
    <add name="MY_SqlContext"
         connectionString="server=.;User Id=root;password=...;Persist Security Info=True;database=MySqlDb;Use Compression=False;Use Old Syntax=False"
         providerName="MySql.Data.MySqlClient" />
    <add name="Ms_SqlContext"
         connectionString="metadata=res://*/Ms_SqlContext.csdl|res://*/Ms_SqlContext.ssdl|res://*/Ms_SqlContext.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=MsSqlDb;User ID=appuser;Password=...&quot;"
         providerName="System.Data.EntityClient" />
</connectionStrings>

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices,  MySql.Data.Entity.EF6" />
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

Code, added to Global.asax.cs: private void Application_Start(object sender, EventArgs e) { DbConfiguration.SetConfiguration(new MySqlEFConfiguration()); ...

After application starts and web requests begin processing trying to create a Ms_SqlContext instance, I get the exception:

An instance of 'MySqlEFConfiguration' was set but this type was not discovered in the same assembly as the 'Ms_SqlContext' context. Either put the DbConfiguration type in the same assembly as the DbContext type, use DbConfigurationTypeAttribute on the DbContext type to specify the DbConfiguration type, or set the DbConfiguration type in the config file. See ...?LinkId=260883 for more information.

Option 3: Set the DbConfiguration type in the configuration file

Appropriate Web.config segments

<connectionStrings>
    <add name="MY_SqlContext"
         connectionString="server=.;User Id=root;password=...;Persist Security Info=True;database=MySqlDb;Use Compression=False;Use Old Syntax=False"
         providerName="MySql.Data.MySqlClient" />
    <add name="Ms_SqlContext"
         connectionString="metadata=res://*/Ms_SqlContext.csdl|res://*/Ms_SqlContext.ssdl|res://*/Ms_SqlContext.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=MsSqlDb;User ID=appuser;Password=...&quot;"
         providerName="System.Data.EntityClient" />
</connectionStrings>

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices,  MySql.Data.Entity.EF6" />
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
</entityFramework>

After application starts and web requests begin processing: ... Ms_SqlContext instance is created, but during first query execution, I get the exception:

EntityException: {"The underlying provider failed on Open."}

InnerException: {"Unable to connect to any of the specified MySQL hosts."}

So, Ms_SqlContext get MySql configuration that is obviously wrong.

Answer 1

So, final solution is:

1. Create own DbConfiguration successor with blackjack and hookers:

       public class MultipleDbConfiguration : DbConfiguration
       {
           #region Constructors 
   
           public MultipleDbConfiguration()
           {
               SetProviderServices(MySqlProviderInvariantName.ProviderName, new MySqlProviderServices());
           }
   
           #endregion Constructors
   
           #region Public methods 
   
           public static DbConnection GetMySqlConnection(string connectionString)
           {
               var connectionFactory = new MySqlConnectionFactory();
   
               return connectionFactory.CreateConnection(connectionString);
           }
   
           #endregion Public methods
       }   
   

2. Mark Ms_SqlContext with MultipleDbConfiguration (and do nothing else with that kind of DbContext)

       [DbConfigurationType(typeof(MultipleDbConfiguration))]
       partial class Ms_SqlContext
       {
       }
   

3. Mark Ms_SqlContext with MultipleDbConfiguration, and ajust MY_SqlContext(string nameOrConnectionString) with call MultipleDbConfiguration.GetMySqlConnection(nameOrConnectionString)

       [DbConfigurationType(typeof(MultipleDbConfiguration))]
       partial class MY_SqlContext : DbContext
       {
                   public MY_SqlContext(string nameOrConnectionString) : base(MultipleDbConfiguration.GetMySqlConnection(nameOrConnectionString), true)
                   {}
       }
   

4. THAT IS IT!!!

Answer 2

You are correct that there is only one DbConfiguration per AppDomain rather than one per context. There is more information on how to specify it here - http://msdn.microsoft.com/en-us/data/jj680699#Moving. If you have multiple configurations and want to be sure the correct one is loaded then you probably want the config file option of the static DbConfiguration.SetConfiguration method.

It looks like MySQL is replacing a bunch of services in the dependency resolver, but the implementations they are registering only work for MySQL.

Code-based configuration is really designed for end developers to set up their config rather than for individual providers to ship a pre-baked one (since there is only one per AppDomain).

My recommendation is to not use theirs, create you own and only register services that you need/want to. For example their execution strategy would be a good thing to register - and registration is provider specific:

SetExecutionStrategy(MySqlProviderInvariantName.ProviderName, () => new MySqlExecutionStrategy());

There will probably be a bit of trail and error as you work out exactly what needs to be registered in order for their provider to work.

Answer 3

FYIW - I had the same problem. The only thing that resolved the issue was to add the following code at the beginning of my program's execution.

var needsToBeInstantiated = new EFDBContextConfiguration();
EFDBContextConfiguration.SetConfiguration( needsToBeInstantiated );

Answer 4

You can use the context constructor that takes a DBConnection and provide the correct connection to the context.

For an example see this question: EF6 and multiple configurations (SQL Server and SQL Server Compact)