Now I know that the Entity Framework has a disconnected data access model i.e. connections to the database and opened and closed only when you retrieve data that has not been retrieved or you explicitly request a call to the database or you call
SaveChanges on the
ObjectContext. I was really surprised to find that inspite of disposing the
ObjectContext, there was still an active connection maintained to the database. The following code fragment shows the typical usage for
Right after this line of code executes, an active connection is established to SQL server.
using statement goes out of scope, the
ObjectContext is disposed, which in turn calls
Close() on the underlying connection object.
"AWAITING COMMAND"? Hmm...that's interesting, you would think that since the
ObjectContext is out of scope it should have been disposed off which in turn would also have closed the underlying database connection. Shouldn't
Close() "close the database connection".
Why do I still see the .NET SqlClient Provider hanging around with a status of "AWAITING COMMAND"?
Is this a bug? or am I just drunk :-)
Turns out that pesky connection pooling is at work here. If you need to explicitly release all active connections you will need to explicitly call either
ClearAllConnections. You could also turn off connection pooling completely by setting Enlist="false".
More info here
Armed with this information, let's see what happens when we call
The output clearly shows that the active connections were terminated and the pooler released the underlying physical connection.
NOTE: The Entity framework uses
EntityConnection and hence calling
ClearPool will not work since it accepts a
The following blurb from MSDN is worth mentioing which helped me get to the bottom of what was going on:
"Connection pooling reduces the number of times that new connections need to be opened.
The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration.
Whenever a user calls Open on a connection, the pooler looks to see if there is an available connection in the pool.
If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of actually closing it.
Once the connection is returned to the pool, it is ready to be reused on the next Open call"
UPDATE(4/5/09) Connection pooling should always be leveraged; it helps to drastically improve performance.
ObjectContext will not release the underlying physical connection, all it does is return the connection back to the pool.
ClearAllPools is being explicitly called here to validate that it(and not disposing of the ObjectContext) is reponsible for closing the underlying physical connection.