Entity Framework 6: Taming the Open Connection Conundrum

By Tyler Holmes

Here’s an Entity Framework scenario for you:

You’re using Entity Framework 6.

Check.

You’re using a custom database initializer to init your DEV and TEST databases. Specifically the DropCreateDatabaseAlways initializer.

Yup, that's me.

Your project contains 1 or more Unit Test projects that utilize the above.

Do you have a unit test project? Say yes.

You have a connection open to the database that the initializer is going to drop.

Say, via SSMS.

You run your unit tests. And it runs, and runs, and runs … and then : 51485cb2b3ab8.preview-620

System.Data.SqlClient.SqlException: Cannot drop database "Foo" because it is currently in use.. Aborting test execution.
Result StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__0(DbCommand t, DbCommandInterceptionContext`1 c)
at

blahdy blah blah blah ...

Ok, so what’s happening? The initializer cannot drop the database because there is currently an open connection. The most simple way of getting rid of open connections is either to delete the DB in your SSMS (with the close all connections checkbox checked), or run some SQL in a query window that accomplishes the same task.

How annoyed are you after the 10th time of doing this? The correct answer is: very.

Give Those Connections a Dirt Nap

There is a fairly simple way to combat this problem by embedding the code necessary to kill open connections directly into the database initializer. Here’s what you do:

In your custom initializer that inherits from DropCreateDatabaseAlways, add the following method (you can rename it to something a little less Class A felony if you wish):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
private void MurderAllConnections(HumanResourceContext context)
{
try
{
// FIRST: Build a connection using the DB Context's current connection.
SqlConnectionStringBuilder sqlConnBuilder = new SqlConnectionStringBuilder(context.Database.Connection.ConnectionString);
// Set the catalog to master so that the DB can be dropped
sqlConnBuilder.InitialCatalog = "master";
using (SqlConnection sqlConnection = new SqlConnection(sqlConnBuilder.ConnectionString))
{
   sqlConnection.Open();
   string dbName = context.Database.Connection.Database;
   // Build up the SQL string necessary for dropping database connections. This statement is doing a couple of things:
   // 1) Tests to see if the DB exists in the first place.
   // 2) If it does, sets single user mode, which kills all connections.
   string sql = @"IF EXISTS(SELECT NULL FROM sys.databases WHERE name = '" + dbName + "') BEGIN ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END";
   using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConnection))
   {
       // Run and done.
       sqlCmd.CommandType = System.Data.CommandType.Text;
       sqlCmd.ExecuteNonQuery();
   }
}
}
catch
{
   // Something bad happened.
   throw new Exception("Hey, boss, the UnitTestInitializer failed. You want I should fix it?");
}
}

Now with the above method in place, the add it to the InitializeDatabase() method in your custom initializer:

1
2
3
4
5
public override void InitializeDatabase(FooContext context)
{
   this.MurderAllConnections(context);
   base.InitializeDatabase(context);
}

Now all those connections are sleepin’ with the fishes.

Some (hopefully obvious) Caveats

  • Make sure the MurderAllConnections() call runs before the base.
  • Do not name your method MurderAllConnections(). That’s just bad form.
  • Your connection will need to be in windows auth mode.
  • Highly recommend NOT using this in a PROD or STAGING environment. Just sayin’.

Happy Entity Frameworking …

Cheers,
Jim

What next?
Let Axian come to the rescue and help define your custom application strategy, develop a roadmap, work with your business community to identify the next project, and provide clarity and direction to a daunting task. For more details about Axian, Inc. and the Custom Application practice click here to view our portfolio or email us directly to setup a meeting.