EntityFramework keeping connections open?

Question

I've an issue caused by connections from my app to the DB, via EF, being open when I don't expect them to be. I'm doing a timing system for sporting events and creating new databases on the fly, copying existing DB files and attaching them using connection strings that are built as needed with the correct filename.

One way of creating a new file is to base it on an existing event, i.e. copy and then purge a load of data. If I try this when the existing event is loaded, I can't copy the file because SQL Server has it locked, and I can't explicitly detach it because SQL Server claims it's in use. When I look at the contents of sys.sysprocesses then I see that yes, EF is maintaining connections that are "AWAITING COMMAND." This is despite me wrapping everything in using() constructs and using an IDbConnectionInterceptor to confirm that my connections are being disposed.

I've managed to break an example down to

using (SportsTimerEntities ctx = new SportsTimerEntities("metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string=\"Data Source=(LocalDB)\\v11.0;AttachDbFilename=C:\\Work\\SportsTimer\\Events\\559eae6a-9974-4463-8546-00824b4aad23.mdf;Integrated Security=True;MultipleActiveResultSets=True;Connect Timeout=30;Application Name=EntityFramework\""))
            {
                dbDevices = ctx.Devices.ToList();
            }

Before the using() block there's no connection. After the block exits, the connection's still there. Despite the IDbConnectionInterceptor confirming that Dispose() has been called.

At the moment the only way I can think of to free the file is to find and kill the sqlservr process and let it restart after doing the file manipulation. This seems both heavy and dangerous. Can anyone suggest a more polite option?

Answer 1

You are facing problem due to SQL connection pooling. This is done by default to optimize performance. SQlConnection is persisted even if you call Dispose and Close on that SqlConnection object. To disable pooling, you have to add pooling to false in connection string

using (SportsTimerEntities ctx = new SportsTimerEntities(
 @"metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;
  provider=System.Data.SqlClient;
  provider connection string=""Data Source=(LocalDB)\\v11.0;
  AttachDbFilename=C:\\Work\\SportsTimer\\Events\\559eae6a-9974-4463-8546-00824b4aad23.mdf;
  Integrated Security=True;
  Pooling=false;
  MultipleActiveResultSets=True;
  Connect Timeout=30;
  Application Name=EntityFramework"""))
{
   dbDevices = ctx.Devices.ToList();
}

Answer 2

As mentioned this is done because you have connection pooling on/enabled which is usually a good thing because connections are expensive to create. You have 2 options.

1. Turn off connection pooling in the connection string, the connection will not be added to the pool. Note that other connections could still exist if connections are made outside of that piece of code or application.
2. Execute SqlConnection.ClearPool which clears the connection pool and will unlock your local db file.

See this article, SQL Server Connection Pooling (ADO.NET), for more information about sql server connection pooling.