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!?

Thanks for posting the solution, that was my exact issue as well. — Woland, Feb 12 '14 at 00:01

score 21 · Accepted Answer · edited Oct 09 '17 at 11:02

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.

score 10 · Answer 2 · answered Oct 13 '16 at 10:50

10

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()
    {

    }
}

answered Oct 13 '16 at 10:50

Alex Pashkin

301
4
15

+1 from me because you are a new in SO and not for the answer ;-) the problem 1) wasnt in the migration 2) it was from the nested transactions. I know that the code above will works but when you put it in the nested transactions it will cause the same problem. – Bassam Alugili Oct 13 '16 at 10:58

Alter Database in Entity Framework 6

2 Answers2

Linked