Geeks With Blogs


Currently Reading

CLR via C#m
Under The Influence(of code) Abhijeet Patel's blog

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 ObjectContext.

Right after this line of code executes, an active connection is established to SQL server.

When the 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 ClearPool or 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 SqlConnection.ClearAllPools.

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 SqlConnection.

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.
Disposing the 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.

kick it on
Posted on Friday, April 3, 2009 10:43 PM Entity Framework , ADO.NET | Back to top

Comments on this post: Entity Framework Object Context - AWAITING COMMAND

# re: Entity Framework Object Context - AWAITING COMMAND
Requesting Gravatar...
I thought connection pooling was a good thing?
Left by Matt Watson on Apr 04, 2009 8:24 PM

# re: Entity Framework Object Context - AWAITING COMMAND
Requesting Gravatar...
@Matt Thanks for bringing this up.Connection pooling is certainly a good thing. I was puzzled by what was causing the connection to stay open and hence the call to ClearAllPools to help mentally validate that it's not the Entity Framework that is holding on to the connection.
I've updated the post to mention that ClearAllPools is not something that you should call on a regular basis
Left by Abhijeet P on Apr 05, 2009 9:28 AM

# re: Entity Framework Object Context - AWAITING COMMAND
Requesting Gravatar...
Hi there,

If we pool is good then we are keeping pool and we should not call ClearAllPools then what is the solution of problem? currently I have a situation where i can see 250+ open connection.
Left by Ashif Nataliya on Jan 10, 2018 9:46 AM

Your comment:
 (will show your gravatar)

Copyright © Abhijeet Patel | Powered by: