13

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.

Chandra Mohan
  • 579
  • 1
  • 6
  • 24
  • 3
    Most likely that is because of connection pooling: opening and closing connections is costly, so .NET holds some connections and reuses them. https://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx – Evk Nov 01 '16 at 12:12
  • I hope so. but in my case i am not reusing the same connection object.So i t will not be useful for me. – Chandra Mohan Nov 01 '16 at 12:17
  • 3
    It'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. – Evk Nov 01 '16 at 12:19
  • make sense. I will confirm and mark as answer – Chandra Mohan Nov 01 '16 at 12:25
  • You can verify that by doing "SqlConnection.ClearPool((SqlConnection) context.Database.Connection);" before disposing your context. Then after dispose your connection should really be closed. – Evk Nov 01 '16 at 12:29
  • Perfect. That's correct. It is because of Pooling – Chandra Mohan Nov 01 '16 at 12:31
  • I guess I couldn't mark as answer until you post it as a answer – Chandra Mohan Nov 01 '16 at 12:33

1 Answers1

21

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.

Evk
  • 84,454
  • 8
  • 110
  • 160