Alter Database in Entity Framework 6

Question

I have update 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 updating I get the following error message:

ALTER DATABASE statement not allowed within multi-statement transaction.

I have fixed the problem with a TransctionalBehavior like the the code below:

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

My question:

• Why I'm getting this error with EF 6?
• My fix is a valid fix for the problem or a devil hiding behind this solution?
• Is there any other approach to solve the problem?

Any help will be greatly appreciated!?

Answer 1

EF 6 changes the use of transactions with ExecuteSqlCommand

Starting with Entity Framework 6.0, ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this method that allow you to override this behavior if you wish.

EF 5 did not behave the same way. Your fix is appropriate.

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

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

By using the DoNotEnsureTransaction flag, EF will not start a transaction before executing the command. This allows the ALTER DATABASE command to successfully execute.

Answer 2

If you are using Code First approach possible solution is

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