在EF6之间切换MySQL / SQL Server [英] Switching between MySQL/SQL Server in EF6

问题描述

我有一个在MySQL和SQL Server上运行的项目。在最近几周内，我已经设法使用基于现有SQL Server架构的数据库优先设计将其从普通旧SQL迁移到实体框架（使用Spring依赖注入）。

现在我需要用MySQL测试。即使我使用适当的MySQL配置，该应用程序将从 MySql.Data.MySqlClient.MySqlConnection ClassCastException c>到 System.Data.SqlClient.SqlConnection

代码：

  //自动连线由Spring 
 public auitool2014Entities DataContext {get; set;} 
 
 public IList< News> FindAllValid（）
 {
返回（来自DataContext.news中的新闻消息，news.annullata == 0选择新闻）.ToList（）; 
} 
  

Spring定义：

 < object id =dataContextsingleton =falsescope =requesttype =DiagnosticoSite.Models.auitool2014Entities，Auitool2014factory-object =dataContextFactoryfactory-method =Builddestroy-method =Dispose>< / object> 
  

春季工厂：

  public auitool2014Entities Build（）
 {
 return new auitool2014Entities（）; 
} 
  

错误消息（从本地化翻译）

 无法将类型为'MySql.Data.MySqlClient.MySqlConnection'的对象转换为键入System.Data.SqlClient.SqlConnection。 
  

跟踪堆栈跟踪：

  [InvalidCastException：无法转换类型为'MySql.Data.MySqlClient.MySqlConnection'的对象类型'System.Data.SqlClient.SqlConnection'。] 
 System.Data.SqlClient.SqlCommand。 set_DbConnection（DbConnection value）+26 
 System.Data.Common.DbCommand.set_Connection（DbConnection value）+9 
 System.Data.Entity.Internal.InterceptableDbCommand.set_DbConnection（DbConnection value）+41 
 System.Data.Common.DbCommand.set_Connection（DbConnection value）+9 
 System.Data.Entity.Core.Common.Utils.CommandHelper.SetStoreProviderCommandState（EntityCommand entityCommand，EntityTransaction entityTransaction，DbCommand storeProviderCommand）+123 
 System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.PrepareEntityCommandBeforeExecution（EntityCommand entityCommand）+314 
 System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreComm ands（EntityCommand entityCommand，CommandBehavior行为）+70 
 System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute（ObjectContext context，ObjectParameterCollection parameterValues）+1283 
 System.Data.Entity.Core。对象。c__DisplayClass7。< GetResults> b__6（）+185 
 System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction（Func`1 func，IDbExecutionStrategy executionStrategy，Boolean startLocalTransaction，Boolean releaseConnectionOnSuccess）+ 448 
 System.Data.Entity.Core.Objects。  c__DisplayClass7。< GetResults> b__5（）+271 
 System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute（Func`1操作）+251 
 System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults（Nullable`1 forMergeOption）+648 
 System.Data.Entity.Core.Objects.ObjectQuery`1。< System.Collections.Generic.IEnumerable< T> .GetEnumerator> b__0（）+68 
 System.Data.Entity.Internal.LazyEnumerator`1.MoveNext（）+ 68 
 System.Collections.Generic.List`1..ctor（IEnumerable`1 collection）+381 
 System.Linq.Enumerable.ToList（IEnumerable`1 source）+58 
 DiagnosticoSite。 d：\Documents\Visual Studio中的Data.Managers.Spring.NewsManagerImpl.FindAllValid（）在Visual Studio 2013中\Projects\Auitool\Auitool2014\Data\Managers\Spring\NewsManagerImpl.cs：16 
  

配置后：

 < entityFramework> 
< defaultConnectionFactory type =System.Data.Entity.Infrastructure.LocalDbConnectionFactory，EntityFramework> 
< parameters> 
< parameter value =v11.0/> 
< / parameters> 
< / defaultConnectionFactory> 
< providers> 
< provider invariantName =MySql.Data.MySqlClienttype =MySql.Data.MySqlClient.MySqlProviderServices，MySql.Data.Entity.EF6，Version = 6.9.5.0，Culture = neutral，PublicKeyToken = c5687fc88969c44d/ > 
< provider invariantName =System.Data.SqlClienttype =System.Data.Entity.SqlServer.SqlProviderServices，EntityFramework.SqlServer/> 
< / providers> 
< / entityFramework> 
< system.data> 
< DbProviderFactories> 
< remove invariant =MySql.Data.MySqlClient/> 
< add name =MySQL数据提供者invariant =MySql.Data.MySqlClientdescription =。用于MySQL的Net Framework数据提供者type =MySql.Data.MySqlClient.MySqlClientFactory，MySql.Data，Version = 6.9.5.0，Culture = neutral，PublicKeyToken = c5687fc88969c44d/> 
< / DbProviderFactories> 
< /system.data> 
  

我基本上将连接字符串更改为MySQL连接字符串

 < add name =myConnectionStringconnectionString =metadata = res：//*/Models.Auitool2014.csdl | res：//*/Models.Auitool2014.ssdl | res：//*/Models.Auitool2014.msl; provider = Mysql.Data.MysqlClient; provider connection string =& quot; Server = localhost; Database = auitemp; Uid = root; Pwd = root; Allow Zero Datetime = True ; CharSet = latin1; Pooling = True; Min Pool Size = 5& providerName =System.Data.EntityClient/> 
  

如何在两个DB之间编译之后切换？我提到了Spring，因为我的 DbContext 是请求 -scoped对象，可以通过工厂对象轻松实例化。 >

调查进度

我发现堆栈跟踪报告了以下类： System.Data .Entity.SqlServer.DefaultSqlExecutionStrategy 。这显然是SQL Server包的一部分。我需要了解EF如何实例化。在EF源代码中，我见

  var executionStrategy = ExecutionStrategy 
 ?? DbProviderServices.GetExecutionStrategy（QueryState.ObjectContext.Connection，QueryState.ObjectContext.MetadataWorkspace）; 
  

真的，这必须是一个 MySql.Data.entity.EF6 .MySqlExecutionStrategy

解决方案

修改EntityFramework的配置

 < entityFramework codeConfigurationType =MySql.Data.Entity.MySqlEFConfiguration，MySql.Data.Entity.EF6> 
< defaultConnectionFactory type =System.Data.Entity.Infrastructure.LocalDbConnectionFactory，EntityFramework> 
< parameters> 
< parameter value =v11.0/> 
< / parameters> 
< / defaultConnectionFactory> 
< providers> 
< provider invariantName =MySql.Data.MySqlClienttype =MySql.Data.MySqlClient.MySqlProviderServices，MySql.Data.Entity.EF6/> 
< provider invariantName =System.Data.SqlClienttype =System.Data.Entity.SqlServer.SqlProviderServices，EntityFramework.SqlServer/> 
< / providers> 
< / entityFramework> 
  

MySql.Data.Entity.MySqlEFConfiguration 钥匙。总结一下，为了从SQL Server切换到MySQL，我必须：

• 编辑连接字符串
  
  
  
  • 提供者属性始终为 System.Data.EntityClient
  
  
  • 在连接字符串中，将提供者更改为 MySql.Data.MySqlClient
  
  


• 替换 entityFramework 标签与上述

I have a project that runs on both MySQL and SQL Server. During the last weeks I have managed to "migrate" it from plain old SQL to Entity Framework (using Spring dependency injection too) using a Database-first design based on the existing SQL Server schema.

Now I need to test with MySQL. Even if I used the "appropriate" MySQL configuration, the app will crash on a ClassCastException from MySql.Data.MySqlClient.MySqlConnection to System.Data.SqlClient.SqlConnection

Code:

//Autowired by Spring
public auitool2014Entities DataContext{get;set;}

public IList<News> FindAllValid()
{
    return (from News news in DataContext.news where news.annullata == 0 select news).ToList();
}

Spring definition:

<object id="dataContext" singleton="false" scope="request" type="DiagnosticoSite.Models.auitool2014Entities, Auitool2014" factory-object="dataContextFactory" factory-method="Build" destroy-method="Dispose" ></object>

Spring factory:

public auitool2014Entities Build()
{
    return new auitool2014Entities();
}

Error message (translated from localized)

Unable to cast object of type 'MySql.Data.MySqlClient.MySqlConnection' to type 'System.Data.SqlClient.SqlConnection'.

Following the stack trace:

[InvalidCastException: Unable to cast object of type 'MySql.Data.MySqlClient.MySqlConnection' type 'System.Data.SqlClient.SqlConnection'.]
   System.Data.SqlClient.SqlCommand.set_DbConnection(DbConnection value) +26
   System.Data.Common.DbCommand.set_Connection(DbConnection value) +9
   System.Data.Entity.Internal.InterceptableDbCommand.set_DbConnection(DbConnection value) +41
   System.Data.Common.DbCommand.set_Connection(DbConnection value) +9
   System.Data.Entity.Core.Common.Utils.CommandHelper.SetStoreProviderCommandState(EntityCommand entityCommand, EntityTransaction entityTransaction, DbCommand storeProviderCommand) +123
   System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.PrepareEntityCommandBeforeExecution(EntityCommand entityCommand) +314
   System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +70
   System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +1283
   System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__6() +185
   System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction(Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess) +448
   System.Data.Entity.Core.Objects.<>c__DisplayClass7.<GetResults>b__5() +271
   System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +251
   System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +648
   System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0() +68
   System.Data.Entity.Internal.LazyEnumerator`1.MoveNext() +68
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +381
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   DiagnosticoSite.Data.Managers.Spring.NewsManagerImpl.FindAllValid() in d:\Documents\Visual Studio 2013\Projects\Auitool\Auitool2014\Data\Managers\Spring\NewsManagerImpl.cs:16

Following the configuration:

  <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, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.5.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

I basically changed my connection string to a MySQL connection string

<add name="myConnectionString" connectionString="metadata=res://*/Models.Auitool2014.csdl|res://*/Models.Auitool2014.ssdl|res://*/Models.Auitool2014.msl;provider=Mysql.Data.MysqlClient;provider connection string=&quot;Server=localhost;Database=auitemp;Uid=root;Pwd=root;Allow Zero Datetime=True;CharSet=latin1;Pooling=True;Min Pool Size=5&quot;" providerName="System.Data.EntityClient" />

How can I switch after compilation between the two DBs? I mentioned Spring because my DbContext is request-scoped object that can be easily instantiated through a factory object.

Investigation progress

I found that the stack trace reports the following class involved: System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy. This obviously looks part of the SQL Server package. I need to understand how EF instantiates that. In EF source code I see

var executionStrategy = ExecutionStrategy
                                    ?? DbProviderServices.GetExecutionStrategy(QueryState.ObjectContext.Connection, QueryState.ObjectContext.MetadataWorkspace);

Really, that must be an instance of MySql.Data.entity.EF6.MySqlExecutionStrategy

解决方案

Fixed changing EntityFramework's configuration

  <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <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>
  </entityFramework>

MySql.Data.Entity.MySqlEFConfiguration is the key. Summarizing, in order to switch from SQL Server to MySQL I have to:

• Edit the damn connection string
  • Provider attribute is always System.Data.EntityClient
  • Within Connection string, change provider to MySql.Data.MySqlClient
• Replace entityFramework tag with the above

这篇关于在EF6之间切换MySQL / SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！