How do I use TransactionScope with MySql and Entity Framework? (getting Multiple simultaneous connections...are not currently supported error)

Question

I have a new .NET 4.0 console application that uses:

• MySql 6.4.4.0
• Entity Framework 4.2 (code-first)
• Visual Studio 2010

So far this works. I can add and read from the database fine.

Now, when I add TransactionScope, as in the following example:

public static void TestInsert()
{
    using (TransactionScope scope = new TransactionScope())
    {
        using (var context = new MyDbContext())
        {
            // Create a test user
            DateTime dt = DateTime.Now;
            var user1 = new User { UserID = 1, UserName = "test" };
            context.Users.Add(user1);  <-- exception occurs here

            context.SaveChanges();
        }
    }
}

When I run this, I get the error:

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

It seems that even the latest version of MySql does not like TransactionScope working with EntityFramework.

I want to be able to use transactions, especially in test projects so that I can roll back any changes.

Any idea how I can fix this or work around it?

Full error information

System.Data.DataException was unhandled
  Message=An exception occurred while initializing the database. See the InnerException for details.
  Source=EntityFramework
  StackTrace:
   at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
   at System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
   at System.Data.Entity.Internal.LazyInternalContext.<InitializeDatabase>b__4(InternalContext c)
   at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input)
   at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action)
   at System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
   at System.Data.Entity.Internal.InternalContext.Initialize()
   at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
   at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
   at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
   at System.Data.Entity.Internal.Linq.InternalSet`1.ActOnSet(Action action, EntityState newState, Object entity, String methodName)
   at System.Data.Entity.Internal.Linq.InternalSet`1.Add(Object entity)
   at System.Data.Entity.DbSet`1.Add(TEntity entity)
InnerException: System.Data.EntityException
   Message=The underlying provider failed on Open.
   Source=System.Data.Entity
   StackTrace:
        at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
        at System.Data.EntityClient.EntityConnection.Open()
        at System.Data.Objects.ObjectContext.EnsureConnection()
        at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
        at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
        at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
        at System.Data.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
        at System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
        at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
        at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
        at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
        at System.Data.Entity.Internal.InternalContext.QueryForModelHash()
        at System.Data.Entity.Internal.InternalContext.CompatibleWithModel(Boolean throwIfNoMetadata)
        at System.Data.Entity.Database.CompatibleWithModel(Boolean throwIfNoMetadata)
        at System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context)
        at System.Data.Entity.Internal.InternalContext.<>c__DisplayClass5.<PerformDatabaseInitialization>b__3()
        at System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action)
    InnerException: System.NotSupportedException
        Message=Multiple simultaneous connections or connections with different connection strings inside the same transaction are not currently supported.
        Source=MySql.Data
        StackTrace:
             at MySql.Data.MySqlClient.MySqlConnection.Open()
             at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
        InnerException: 

Some of the classes (nothing complicated):

[Table("User")]
public class User
{
    public User()
    {
    }

    // Primary key
    [Key]
    public int UserID { get; set; }
    public string UserName { get; set; }
}


public class MyDbContext : DbContext
{
    public MyDbContext() : base("DbContext")
    {
    }

    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Tell Code First to ignore PluralizingTableName convention
        // If you keep this convention then the generated tables will have pluralized names.
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

App.config:

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <clear/>
    <add name="DbContext" connectionString="Server=localhost; Database=****; Uid=****; Pwd=****;" providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
  <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"/>
    </DbProviderFactories>
  </system.data>
</configuration>

Thanks in advance,

Dan

Answer 1

Try this, Create transaction after creating context.

public static void TestInsert()
{
    using (var context = new MyDbContext())
    {
        using (TransactionScope scope = new TransactionScope())
        {
            // Create a test user
            DateTime dt = DateTime.Now;
            var user1 = new User { UserID = 1, UserName = "test" };
            context.Users.Add(user1); 

            context.SaveChanges();

            scope.Complete();
        }
    }
}

Answer 2

Kindly complete the scope.

The Complete method commits the transaction. If an exception has been thrown,

Complete is not called and the transaction is rolled back.

 scope.Complete();

See MSDN.