相同的应用，不同的数据库:Entity framework 6.X + MySQL + SQL Server

2022-01-20 00:00:00 mysql sql-server entity-framework-6

昨天我进行了 Web 应用程序的迁移 (EF 5.0 => EF 6.0)，该应用程序使用实体框架来访问 MySql 和 SQL Server 数据库(特定的 DbContext 到特定的数据库，而不是任何 DbContext 到任何类型的数据库).

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:

在发现MySqlEFConfiguration"类型之前，实体框架使用了默认的 DbConfiguration 实例.

The default DbConfiguration instance was used by the Entity Framework before the 'MySqlEFConfiguration' type was discovered.

上下文的 [DbConfigurationType(typeof(MySqlEFConfiguration))] 属性在运行时似乎已被忽略，因为上下文位于外部程序集中 (?) 并且 DbConfiguration<使用的/code> 对应用程序域来说是全局的，而不是特定于上下文(?)."

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.

看起来这里描述的情况很好 http://forums.mysql.com/read.php?174,614148,614148 还是没变，还是漏掉了一些明显的东西.

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.

提前谢谢你！

详细说明:

输入(简化):- ASP.NET 网络应用程序

Input (simplified): - ASP.NET Web Application

  • 在 Entity Framework 6.1.1 上实现的数据访问层

  • Data access layer implemented over Entity Framework 6.1.1

实体框架提供者:

  • System.Data.SqlClient 6.1.1

  • System.Data.SqlClient 6.1.1

MySql.Data.MySqlClient 6.9.4

MySql.Data.MySqlClient 6.9.4

MY_SqlContext，模型优先的概念，针对 MY SQL Server 数据库

MY_SqlContext, model first concept, targeted to MY SQL Server database

Ms_SqlContext，数据库优先概念，针对 MS SQL Server 数据库

Ms_SqlContext, database first concept, targeted to MS SQL Server database

根据 Entity Framework 6 的通用文档和 MySql Connector/Net 文档(http://dev.mysql.com/doc/connector-net/en/connector-net-entityframework60.html),MY_SqlContext 需要应用 MySqlEFConfiguration.

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.

选项 1:将 DbConfigurationTypeAttribute [DbConfigurationType(typeof(MySqlEFConfiguration))] 添加到 MY_SqlContext 类

Option 1: Adding the DbConfigurationTypeAttribute [DbConfigurationType(typeof(MySqlEFConfiguration))] to MY_SqlContext class

适当的 Web.config 段:

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 工作正常，但尝试创建 MY_SqlContext 实例时出现异常:

Ms_SqlContext works fine but trying to create a MY_SqlContext instance, I get the exception:

在发现MySqlEFConfiguration"类型之前，Entity Framework 使用了默认的 DbConfiguration 实例.在使用任何实体框架功能之前，必须在应用程序启动时设置MySqlEFConfiguration"实例，或者必须在应用程序的配置文件中注册.有关详细信息，请参阅 ...LinkId=260883."

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

方案二:在应用启动时调用 DbConfiguration.SetConfiguration(new MySqlEFConfiguration())

Option 2: Calling DbConfiguration.SetConfiguration(new MySqlEFConfiguration()) at the application startup

适当的 Web.config 段(实际上与选项 1 相同):

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>

代码，添加到 Global.asax.cs:private void Application_Start(object sender, EventArgs e){DbConfiguration.SetConfiguration(new MySqlEFConfiguration());...

Code, added to Global.asax.cs: private void Application_Start(object sender, EventArgs e) { DbConfiguration.SetConfiguration(new MySqlEFConfiguration()); ...

在应用程序启动并开始处理 Web 请求以尝试创建 Ms_SqlContext 实例后，我收到异常:

After application starts and web requests begin processing trying to create a Ms_SqlContext instance, I get the exception:

设置了MySqlEFConfiguration"实例，但未在与Ms_SqlContext"上下文相同的程序集中发现此类型.要么将 DbConfiguration 类型放在与 DbContext 类型相同的程序集中，使用 DbContext 类型上的 DbConfigurationTypeAttribute 来指定 DbConfiguration 类型，要么在配置文件中设置 DbConfiguration 类型.有关详细信息，请参阅 ...?LinkId=260883.

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.

选项3:在配置文件中设置DbConfiguration类型

Option 3: Set the DbConfiguration type in the configuration file

适当的 Web.config 段

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>

应用程序启动并开始处理 Web 请求后:...创建了 Ms_SqlContext 实例，但在第一次查询执行期间，我得到了异常:

After application starts and web requests begin processing: ... Ms_SqlContext instance is created, but during first query execution, I get the exception:

EntityException: {"底层提供程序在打开时失败."}

EntityException: {"The underlying provider failed on Open."}

InnerException: {"无法连接到任何指定的 MySQL 主机."}

InnerException: {"Unable to connect to any of the specified MySQL hosts."}

那么，Ms_SqlContext 获取的 MySql 配置显然是错误的.

So, Ms_SqlContext get MySql configuration that is obviously wrong.

推荐答案

所以，最终的解决方案是:

So, final solution is:

  1. 用二十一点和妓女创建自己的 DbConfiguration 继任者:

  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
    }

  • 用 MultipleDbConfiguration 标记 Ms_SqlContext(对那种 DbContext 什么也不做)

  • Mark Ms_SqlContext with MultipleDbConfiguration (and do nothing else with that kind of DbContext)

        [DbConfigurationType(typeof(MultipleDbConfiguration))]
    partial class Ms_SqlContext
    {
    }

  • 使用 MultipleDbConfiguration 标记 Ms_SqlContext，并通过调用 MultipleDbConfiguration.GetMySqlConnection(nameOrConnectionString) 调整 MY_SqlContext(string nameOrConnectionString)

  • 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)
                {}
    }

  • 就是这样！！！

  • THAT IS IT!!!

    • 相关文章