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."
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 ContextList
{
get { return _contextList; }
}
private readonly IEnumerable _contextList;
public UnitOfWork(IEnumerable 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.