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?
