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?

Solution for EF 6.X http://stackoverflow.com/questions/26361592/same-application-different-databases-entity-framework-6-x-mysql-sql-server/26496907#26496907 — Artem Ozornin, Oct 21 '14 at 22:04
I got i working using Entity Framework 6 see [Use MySQL and MSSQL in Entity Framework 6][1] [1]: http://stackoverflow.com/questions/31280119/use-mysql-and-mssql-in-entity-framework-6/31314625#31314625 — Daniel, Jul 09 '15 at 10:38
I got it working using Entity Framework 6 see [Use MySQL and MSSQL in Entity Framework 6][1] [1]: http://stackoverflow.com/questions/31280119/use-mysql-and-mssql-in-entity-framework-6/31314625 — Daniel, Jul 09 '15 at 10:41

score 3 · Answer 1 · answered May 07 '14 at 19:47

3

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

answered May 07 '14 at 19:47

Danilo Breda

1,092
1
15
30

I put here only for mysql because it can be useful for users like me.. that uses only Mysql, please put the UPDATE of the resolution of this problem("Using 2 Databases type") after. – Danilo Breda May 07 '14 at 19:51
Thanks for the input, you are right this does help when solely using EF6 and MySQL but does not solve my problem. – James May 08 '14 at 09:29

James · Accepted Answer · 2014-07-17T09:42:30.237

1

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.

edited Jul 17 '14 at 09:42

answered Jun 09 '14 at 09:08

James

926
1
8
24

Im trying to make the SQLCE and Mysql work on the same solution... using entity framework and migrations... but i didnt understand how you make it run. You make 2 config files? – Danilo Breda Jul 15 '14 at 18:28
1

@Daniloko I have updated the answer, hopefully this will provide you with what you need. – James Jul 17 '14 at 09:43

score 0 · Answer 3 · answered Aug 13 '14 at 05:48

I had the same issue. I'm using migrations in my code first project with two db contexts for mysql and mssql. The issue is because EF6 tries to find db configuration in config file or in the same assembly where db context is. For mysql migrations I use MySqlHistoryContext that inherits from DbContext. During db initialization stage EF6 creates instance of MySqlHistoryContext and find MySqlEFConfiguration type and tries to use it. I found two ways to solve the issue. The first is disabling migrations checks by call Database.SetInitializer with null argument. The second is inherit from MySqlHistoryContext in my own assembly. The second seems to work for me.

score 0 · Answer 4 · answered Jan 05 '17 at 19:34

I chased down all the solutions I could find on SO and elsewhere, and for me the issue turned out to be my MSSQL connection string.

before:

<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Server=serverName/instance;Database=PublicWebsite;Integrated Security=True;" />

after:

<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=serverName/instance;Initial Catalog=PublicWebsite;Integrated Security=True;" />

The "before" connection string worked fine for EF. The problem presented after we introduced MySQL for EF (in a different class library).

To be clear, in MSSQL the connection string we just changed "server" to "data source" and we changed "database" to "initial catalog".

Using MySQL & MSSQL for two different databases with Entity Framework

4 Answers4

Linked