Using MySQL & MSSQL for two different databases with Entity Framework

Question

I am trying to build a web api that serves data from either a MySQL database or a MSSQL database (completely different data and schema). It also provides some endpoints for moving data between the two. I have created two class libraries to hold the EF models for both databases and have been successful in connecting to both instances and running queries against both. Where it falls down is trying to access both of them. I have to tweak the web.config entityFramework section to get either to work, I can't get it so that both work at the same time effectively. I am using EF6 and the latest MySQL connector.

This is the working config for MSSQL entities:

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="v11.0" />
  </parameters>
</defaultConnectionFactory>
<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>

This is the error it produces when I try to use the MySQL entity context

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.

This is the working config for MySQL entities:

<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" />  

This then complains about the connection string as its trying to use the MySQL libraries. I can't find much online about this, this question here is similar but there was no answer to it: Is it possible to have a EF Context for MySql and SqlServer?

Has anyone done this or know of a way round the issue?

Answer 1

To resolve this error i put this on my App.config: "codeConfigurationType"

<entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">

Why? Possible the Configurations is not fiding the location of MySqlEFConfiguration.

Im using here only Mysql, i dont know if this work on SQLServer and Mysql togueter.

For your problem this link can be useful: Link

And you can have 2 separeted configuration.cs Files. One for MySql and other for MSSQL

Answer 2

The issue appears to have been addressed in the beta MySQL connector at the time of writing this, version 6.9.1. I now have the two running side by side without any problems.

Update

For clarification the setup I have is EF5 (EF6 won't work) and the MySQL connector version 6.9.1 (beta). my web.config section looks like this:

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

The model's are located in the same project, again I have had difficulty with class libraries.