EF DBContext does not close the connection

Question

I am using EF 6.1.0

I have below a custom DBContex object as DBEntites

public partial class DbEntities : DbContext { public DbEntities() : base("name=DbEntities") { ////Configuration.LazyLoadingEnabled = true; ////Configuration.ProxyCreationEnabled = false; } //// I have ALL Entites added as below ////public virtual IDbSet<CCode> CCodes { get; set; } } 

I have the following operations on a context object

 using (var context = new DbEntities()) { var entitySet = context.Set<T>(); var res = entitySet.Where<T>(predicate).ToList(); if (context.Database.Connection.State == ConnectionState.Open) { context.Database.Connection.Close(); } return res; } 

But after deleting the context object, you can still see the active connection to the database. In the state of the connection state, I see that the connection is already closed (the connection has never been true).

I use the following query to see the connection to SQL.

 select db_name(dbid) , count(*) 'connections count' from master..sysprocesses where spid > 50 and spid != @@spid group by db_name(dbid) order by count(*) desc 

In the description below, the number of sql connections has increased. But it never fell even after disposal. (I mean, after using a block from it, it is supposed to close the connection).

 var res = entitySet.Where<T>(predicate).ToList(); 

Any help would be greatly appreciated.

Answer 1

As it turned out in the comments, the reason is pooling, performed by .NET..NET maintains a connection pool for each connection string that you use in your application for performance reasons (since opening and closing connections can often be costly in terms of performance). This pool has a minimum and maximum size (controlled by the string parameters MinPoolSize and MaxPoolSize ). When you open a connection (via SqlConnection.Open ), you can remove it from the pool and not reopen it. When you close a connection (which is also done by removing the EF context), the connection can be put into the pool instead and not closed. When the connection is idle for a certain time (about 5 minutes) - it can be removed from the pool.

If you (for some reason) want to avoid this, you can either set MaxPoolSize to 0 for your connection string, or explicitly empty the SqlConnection.ClearPool or SqlConnection.ClearAllPools pool.