EF DBContext dispose not closing the connection
As was figured out in comments, the reason is indeed connection pooling performed by .NET. .NET maintains a pool of connections for every connection string you use in your application, for perfomance reasons (because opening and closing connections often might be costly in terms of perfomance). That pool has certain minimum and maximum size (controlled by MinPoolSize
and MaxPoolSize
connection string parameters). When you open a connection (via SqlConnection.Open
) - it might be taken out of the pool and not really opened afresh. When you close connection (which is also done by disposing EF context) - connection might be put into the pool instead, and not really closed. When connection is idle for certain time (about 5 minutes) - it might be removed from the pool.
If you (for some reason) want to avoid that, you can either set MaxPoolSize to 0 for your connection string, or clear pool explicitly by SqlConnection.ClearPool
or SqlConnection.ClearAllPools
.
Related videos on Youtube
Chandra Mohan
Updated on July 09, 2022Comments
-
Chandra Mohan almost 2 years
I am using the EF 6.1.0
I have below 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 below operations on 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 disposing the context object still i can see a active DB Connection. On connection state condition i can see that the connection is already closed(the connection had never true).
I am using the below query to see the connection on 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
At the below statement a sql connection count increased. But it was never down even after disposing . (I mean after using block excuted it supposed to close the connection).
var res = entitySet.Where<T>(predicate).ToList();
Any help would be greatly appreciated.
-
Evk over 7 yearsMost likely that is because of connection pooling: opening and closing connections is costly, so .NET holds some connections and reuses them. msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx
-
Evk over 7 yearsIt's not about reusing connection object. When you create new connection with the same configuration (connection string), it will not really open new connection to database, but reuse existing one (if there is one in the pool). Same when you close connection object - underlying connection might not be closed but instead put into the pool. Since you most likely will query the same database more than once through the lifetime of your application - that will be useful for you.
-