将MySQL和MSSQL用于具有Entity Framework的两个不同数据库

时间:2022-09-21 23:49:19

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.

我正在尝试构建一个web api,它提供来自MySQL数据库或MSSQL数据库(完全不同的数据和模式)的数据。它还提供了一些在两者之间移动数据的端点。我已经创建了两个类库来保存两个数据库的EF模型,并且已成功连接到两个实例并针对这两个实例运行查询。它倒下的地方是试图访问它们。我必须调整web.config entityFramework部分以使其工作,我无法得到它,以便两者有效地同时工作。我正在使用EF6和最新的MySQL连接器。

This is the working config for MSSQL entities:

这是MSSQL实体的工作配置:

<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

这是我尝试使用MySQL实体上下文时产生的错误

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:

这是MySQL实体的工作配置:

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

然后,这会尝试连接字符串,因为它试图使用MySQL库。我在网上找不到这个问题,这个问题类似但是没有答案:是否可以为MySql和SqlServer设置EF上下文?

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

有没有人这样做或知道解决问题的方法?

4 个解决方案

#1


3  

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

要解决此错误,我将其放在我的App.config上:“codeConfigurationType”

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

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

为什么?可能配置不是伪造MySqlEFConfiguration的位置。

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

我在这里只使用Mysql,我不知道这是否适用于SQLServer和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

你可以有2个separeted configuration.cs文件。一个用于MySql,另一个用于MSSQL

#2


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.

在编写此版本6.9.1时,问题似乎已在beta MySQL连接器中得到解决。我现在有两个并排运行没有任何问题。

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:

为了澄清我的设置是EF5(EF6不起作用)和MySQL连接器版本6.9.1(beta)。我的web.config部分如下所示:

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

模型位于同一个项目中,我再次遇到类库困难。

#3


0  

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.

我遇到过同样的问题。我在我的代码第一个项目中使用迁移,其中包含两个用于mysql和mssql的db上下文。问题是因为EF6尝试在配置文件或db上下文所在的同一程序集中查找数据库配置。对于mysql迁移,我使用继承自DbContext的MySqlHistoryContext。在db初始化阶段,EF6创建MySqlHistoryContext的实例并找到MySqlEFConfiguration类型并尝试使用它。我找到了两种解决问题的方法。第一种是通过使用null参数调用Database.SetInitializer来禁用迁移检查。第二个是继承自我自己的程序集中的MySqlHistoryContext。第二个似乎对我有用。

#4


0  

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.

我追查了我在SO和其他地方找到的所有解决方案,对我而言,问题原来是我的MSSQL连接字符串。

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).

“之前”连接字符串适用于EF。在我们为EF引入MySQL(在不同的类库中)之后出现了这个问题。

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

为了清楚起见,在MSSQL中我们刚刚将“服务器”更改为“数据源”的连接字符串,我们将“数据库”更改为“初始目录”。

#1


3  

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

要解决此错误,我将其放在我的App.config上:“codeConfigurationType”

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

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

为什么?可能配置不是伪造MySqlEFConfiguration的位置。

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

我在这里只使用Mysql,我不知道这是否适用于SQLServer和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

你可以有2个separeted configuration.cs文件。一个用于MySql,另一个用于MSSQL

#2


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.

在编写此版本6.9.1时,问题似乎已在beta MySQL连接器中得到解决。我现在有两个并排运行没有任何问题。

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:

为了澄清我的设置是EF5(EF6不起作用)和MySQL连接器版本6.9.1(beta)。我的web.config部分如下所示:

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

模型位于同一个项目中,我再次遇到类库困难。

#3


0  

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.

我遇到过同样的问题。我在我的代码第一个项目中使用迁移,其中包含两个用于mysql和mssql的db上下文。问题是因为EF6尝试在配置文件或db上下文所在的同一程序集中查找数据库配置。对于mysql迁移,我使用继承自DbContext的MySqlHistoryContext。在db初始化阶段,EF6创建MySqlHistoryContext的实例并找到MySqlEFConfiguration类型并尝试使用它。我找到了两种解决问题的方法。第一种是通过使用null参数调用Database.SetInitializer来禁用迁移检查。第二个是继承自我自己的程序集中的MySqlHistoryContext。第二个似乎对我有用。

#4


0  

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.

我追查了我在SO和其他地方找到的所有解决方案,对我而言,问题原来是我的MSSQL连接字符串。

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).

“之前”连接字符串适用于EF。在我们为EF引入MySQL(在不同的类库中)之后出现了这个问题。

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

为了清楚起见,在MSSQL中我们刚刚将“服务器”更改为“数据源”的连接字符串,我们将“数据库”更改为“初始目录”。