CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
Chandra Mohan
Chandra Mohan

Reputation: 771

EF DBContext dispose not closing the connection

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.

Upvotes: 24

Views: 28894

Answers (2)

Thiago Ara&#250;jo
Thiago Ara&#250;jo

Reputation: 280

Just to register my experience here. I had same problem with EF Core 6 + Postgres and the solution was just to disable "Pooling" in the connection string.

Pooling=false;

Upvotes: -2

Evk
Evk

Reputation: 101483

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.

Upvotes: 31

Related Questions