TransactionScope与EntityFramework 6和MySql [英] TransactionScope with EntityFramework 6 and MySql

问题描述

我对项目有以下要求：使用EntityFramework在MySql中的多个数据库进行预处理事务（所有数据库都在同一个mysql服务器上）。

解决TransactionScope的问题，似乎有不同的MySql连接字符串的问题：

多个同时连接或连接与不同的连接字符串目前不支持同一事务内部的支持。

这里已经描述了这个问题（没有任何具体的解决方案）：
如何在MySql和Entity Framework中使用TransactionScope？ （获得多个并发连接...目前不支持错误）

作为一种解决方法，我尝试从连接字符串中删除数据库参数，这可以用于我打开连接并在调用.SaveChanges（）方法之前设置数据库（通过在继承自DbContext的类中重载方法）。但是，为每个select语句调用相同的语句是不可行的。

我的自定义类看起来像这样：

  public class ContextBase：DbContext 
 {
 public ContextBase（string connectionStringWithoutDatabase）
：base（connectionStringWithoutDatabase）
 {} 
 
 public override int SaveChanges（）
 {
 Database.Connection.Open（）; 
 Database.Connection.ChangeDatabase（MyDatabaseName）; 
 base.SaveChanges（）; 
 Database.Connection.Close（）; 
} 
 
 //如何处理选择？ 
} 
  

我的工作单位：

  public class UnitOfWork 
 {
 private IEnumerable< DbContext> ContextList 
 {
 get {return _contextList; } 
} 
 
 private readonly IEnumerable< DbContext> _contextList; 
 public UnitOfWork（IEnumerable< DbContext> contextList）
 {
 _contextList = contextList; 
} 
 
 public void Save（）
 {
 var transactionScope = new TransactionScope（）; 
 foreach（ContextList中的DbContext上下文）
 {
 context.SaveChanges（）; 
} 
 transactionScope.Complete（）; 
 transactionScope.Dispose（）; 
} 
} 
  

另一种可能的解决方法是创建一个包装器，其中将有两个DbContext实例 - 一个数据库为select语句设置，另一个不用于非查询操作。但是这只是感觉错了。

现在我的问题：

• 是否有更简单的方式进行此类交易？




• 可以在select语句发生之前设置数据库名称吗？打开构造函数中的连接是否可行？




• 两个DbContexts的包装器是否太多开销？

解决方案

我们最终找到了在每次选择之前设置数据库名称的问题的解决方案。要实现这一点，必须创建一个实现 IDbCommandInterceptor 的类，并将其注册到您的上下文中。在该接口的不同功能中，您可以在SQL发送到服务器之前更改数据库。
粗略的测试也显示没有明显的性能下降。

I have the following requirement for a project: preform transactions over multiple databases in MySql with EntityFramework (all databases are on the same mysql server).

When trying to solve the issue with TransactionScope, there seems to be an issue with different MySql connection strings:

"Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported."

The issue is already described here (without any concrete solution): How do I use TransactionScope with MySql and Entity Framework? (getting Multiple simultaneous connections...are not currently supported error)

As a workaround I tried ommiting the database parameter from the connection string, which works if I open the connection and set the database before the .SaveChanges() method is called (by overloading the method in a class that inherits from DbContext). But calling the same statement for each select statement is just not feasable.

My custom class looks like this:

public class ContextBase : DbContext
{
    public ContextBase(string connectionStringWithoutDatabase)
        : base(connectionStringWithoutDatabase)
    {}

    public override int SaveChanges()
    {
        Database.Connection.Open();
        Database.Connection.ChangeDatabase("MyDatabaseName");
        base.SaveChanges();
        Database.Connection.Close();
    }

    // How to handle Selects?
}

My Unit of work class:

public class UnitOfWork
{
    private IEnumerable<DbContext> ContextList
    {
        get { return _contextList; }
    }

    private readonly IEnumerable<DbContext> _contextList;
    public UnitOfWork(IEnumerable<DbContext> contextList)
    {
        _contextList = contextList;
    }

    public void Save()
    {
        var transactionScope = new TransactionScope();
        foreach (DbContext context in ContextList)
        {
            context.SaveChanges();
        }
        transactionScope.Complete();
        transactionScope.Dispose();
    }
}

Another possible workaround would be to create a wrapper in which there would be two DbContext instances - one with the database set for select statements, the other without for the non-query operations. But this just feels wrong.

Now my question(s):

• Is there an easier way to do such transactions?

• Is it possible to set the database name before an select statement occurs? Is opening the connection in the constructor feasable?

• Would the wrapper with two DbContexts be too much overhead?

解决方案

We ended up finding a solution to the problem of setting the database name before each select. To achieve this one must create a class that implements IDbCommandInterceptor and register it with your context. In the different functions of that interface you can change the Database before the SQL is sent to the server. A rough test also showed no noticable performance degradation.

这篇关于TransactionScope与EntityFramework 6和MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！