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

entity-framework-6 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.

1
12
3/30/2016 1:05:06 AM

Popular Answer

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

6
10/21/2014 9:44:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow