Using Entity Framework with Multiple Databases and Providers in the Same Project (SQL Server and MySql)

.net c# entity-framework entity-framework-6 mysql-connector

Question

Background:

I have an application (both web forms and console) that easily accesses multiple SQL Server databases using Entity Framework code first with no problems. I can switch back and forth between contexts with no errors. Now I'm trying to add in a new MySql database (different provider), but if I switch from one context to another, I get the below error message:

Error:

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 http://go.microsoft.com/fwlink/?LinkId=260883 for more information.

I've seen solutions that recommend adding the "codeConfigurationType" attribute to the entityFramework node in the web.config to specify my MySql assembly, but this won't work since I'm targeting multiple databases with multiple providers.

The Microsoft support article seems to hint at creating my own custom DbConfiguration file, but I already have one: MySql.Data.Entity.mySqlEFConfiguration, and I've decorated my DbContext with it accordingly:

   [DbConfigurationType(typeof(MySqlEFConfiguration))]
    public class MySqlDBContext : DbContext
    {
        public MySqlDBContext() : base("MySqlDBContext")
        {

        }
    }
...

Here's how I have my web.config set up:

  <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>
  <connectionStrings>        
    <add name="FirstSQLServerDBContext" providerName="System.Data.SqlClient" connectionString="Server=SQLServerName;MultipleActiveResultSets=True; Database=FirstSQLDB; User ID=SQLUsername;Password=SqlPassword; Encrypt=true;" />
    <add name="SecondSQLServerDBContext" providerName="System.Data.SqlClient" connectionString="Server=SQLServerName;MultipleActiveResultSets=True; Database=SecondSQLDB; User ID=SQLUsername;Password=SqlPassword; Encrypt=true;" />
    <add name="MySqlDBContext" providerName="MySql.Data.MySqlClient" connectionString="server=MySqlServerName; port=3306;database=MySqlDB; uid=MySqlUsername; password=MySqlPassword;" />    
  </connectionStrings>

Question:

It doesn't seem like there's any way to do this using config file configuration since I'm limited to a single entry. So how can I specify the provider when switching contexts? I imagine there's something I'm missing in each DbContext's constructor to explicitly state which provider to use? For the record, I'm receiving the same error message in both a console application and a web forms application.

Update: Additional Info

  • (8/21/19) It seems to work okay if I disable MultipleActiveResultSets (MARS) on the SQLServer data sources, though this isn't an ideal solution since I've written my code to take advantage of MARS.
  • (8/22/19) Actually that wouldn't have solved it. It would have just gone on to the next problem. There can only be one DbConfiguration per application, so the solution will likely involve writing one that works with both providers... I've submitted a support request with my MSDN subscription to see if they have any additional insight...
1
1
8/22/2019 7:37:15 PM

Accepted Answer

With the help of Microsoft, I finally figured it out. Hopefully this helps someone else out there. I had to create my own class inheriting from DbConfiguration

public class MyCustomSQLDbConfiguration : DbConfiguration
{
    public MyCustomSQLDbConfiguration()
    {
        SetExecutionStrategy("MySql.Data.MySqlClient", () => new MySqlExecutionStrategy());
        SetDefaultConnectionFactory(new LocalDbConnectionFactory("mssqllocaldb"));
    }
}

Then decorate my MySqlDBContext accordingly:

[DbConfigurationType(typeof(MyCustomSQLDbConfiguration))]
public class MySqlDBContext : DbContext
{
    public MySqlDBContext() : base("MySqlDBContext")
    {

    }
 }

And then finally (and importantly), explicitly set this when I run the application. In the case of a console app, at the beginning of Program.Main, or in a web application, on Application_Start:

DbConfiguration.SetConfiguration(new MyCustomSQLDbConfiguration());
1
9/13/2019 7:32:18 PM

Popular Answer

you can use a partial class:

 public partial class MySqlDBContext : DbContext
    {
        public MySqlDBContext(string FirstSQLServerDBContext)
            : base(configmanager.connectionstrings(FirstSQLServerDBContext)...)
        {
        }


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