How to avoid a "Nested transactions are not supported." error?

Question

I am using EF6 to do some pretty simple integration with a MySql database.

The Nested transactions are not supported. error occurs after I do the following:

1. Attempt to add a key that already exists... Which leads to the error: Duplicate entry 'asdf' for key 'UserName_UNIQUE'
2. Attempt to add anything afterwards... Which leads to the error: Nested transactions are not supported.

I guess I'm not sure what would be Nested about these two queries... What am I doing wrong:

And for some code

using (var db = C2SCore.BuildDatabaseContext())
{
  db.Users.Add(new UserProfile { UserName = UserName, Password = Password });
  db.SaveChanges(); // <- Errors occur here...
}

This snippet runs (as my the flow described above implies) for every UserProfile I add.

Answer 1

I have exactly the same problem. Try the following workaround by wrapping it into a TransactionScope:

using System.Transactions; // Add assembly in references

using (var db = C2SCore.BuildDatabaseContext())
{
  using (var tran = new TransactionScope())
  {
    db.Users.Add(new UserProfile { UserName = UserName, Password = Password });
    db.SaveChanges(); // <- Should work now after first exception
    tran.Complete();
  }
}

<package id="MySql.Data" version="6.8.3" targetFramework="net45" />
<package id="MySql.Data.Entities" version="6.8.3.0" targetFramework="net45" />

However, they are aware of it: http://bugs.mysql.com/bug.php?id=71502