Editing a database in Entity Framework 6

Question

I upgraded my EF to EF 6.0.2 in my code. I have the following line of code:

applicationDbContext.Database .ExecuteSqlCommand(@"ALTER DATABASE CURRENT SET RECOVERY FULL;"); 

After the update, the following error message appears:

The ALTER DATABASE attribute is not allowed in multiple transaction statements.

I fixed the problem with TranscendalBehavior, like the code below:

 applicationDbContext.Database.ExecuteSqlCommand( TransactionalBehavior.DoNotEnsureTransaction, @"ALTER DATABASE CURRENT SET RECOVERY FULL;"); 

My question is:

• Why am I getting this error with EF 6?
• Is my fix a valid fix for the problem or the devil lurking behind this solution?
• Is there any other approach to solving the problem?

Any help would be greatly appreciated !?

Answer 1

EF 6 changes transaction usage using ExecuteSqlCommand

Starting with Entity Framework 6.0, ExecuteSqlCommand () by default transfers the command to the transaction, if it does not already exist. There are overloads of this method that allow you to override this behavior if you want.

EF 5 does not behave the same. Your fix is ​​appropriate.

You can now specify the TransactionalBehavior flag to instruct EF on how to handle transactions using this command.

 var sqlCommand = String.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE"); db.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, sqlCommand); 

Using the DoNotEnsureTransaction flag, EF does not start the transaction before executing the command. This allows you to successfully execute the ALTER DATABASE command.

Answer 2

If you use the Code First approach, maybe a solution

 public partial class AlterDatabase : DbMigration { public override void Up() { Sql("ALTER DATABASE CURRENT SET RECOVERY FULL", true); } public override void Down() { } }