Dan Bartram
I recently upgraded a project from EF 5 to EF 6 which immediately caused every integration test to fail. Checking the logs and launching the tests in the debugger, I discovered that an exception was being thrown when running the following SQL command:
?
1

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

When this line is executed, an exception is thrown:

?
1
System.Data.SqlClient.SqlException :
ALTER DATABASE statement not allowed within multi-statement transaction.

I use the above command as part of a method to force drop the database for testing purposes. That particular command forces any connections other than the current one to drop so the database can be dropped. This allows the integration tests to start with a clean database where test data can be inserted.

Since this code worked flawlessly with EF 5, I figured something had changed in EF 6. Prior to upgrading, I read over the Upgrade Guide, but it mentions nothing about changes to ExecuteSqlCommand. However, after tiny bit of GoogleFu, I found some additional notes on EF6 changes.

It states, "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."

Now, certain SQL commands must run in autocommit mode. The ALTER DATABASE command is one of them. Thus, it is not allowed to run within either an explicit or implicit transaction. This explains why this worked under EF5 but not EF6.

Checking into the overloads of ExecuteSqlCommand(), you can now specify a TransactionalBehavior flag to instruct EF on how to handle transactions with this command.

?
1

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.