When does EF Core DBContext dispose Sql Connection?

Question

When using classic ADO.NET we typically close the SQL connection as soon as we are done executing the SQL command, so the connection is closed and returned back to the pool.

Something like:

public Order[] GetActiveOrders()
{
        var orders = new List();

        using (SqlConnection connection = new SqlConnection("connectionString"))
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "Select * FROM ORDERS WHERE Status = 'Active'";
                cmd.Connection.Open();

                using (var reader = cmd.ExecuteReader())
                {
                    //populate orders
                }
            }
        }
        
        // SQL connection is closed here and returned back to connection pool
        
        return orders;
}

In ASP.NET Core using EF Core, we typically inject the DbContext into constructor using DI framework

      public class OrderService:IDisposable
      {
         private readonly MyDBContext _dbContext;
         
         public OrderService(MyDBContext dbContext)
         {
             _dbContext = dbContext;
         }
         
         public Order[] GetActiveOrders()
         {
             var orders = _dbContext.Orders.Where(x=>x.Status == 'Active').ToArray()
             return orders;
         }
         
         #region Dispose
         public void Dispose()
         {
              Dispose(true);
              GC.SuppressFinalize(this);
         }
    
         protected virtual void Dispose(bool disposing)
         {
              if (_disposed)
                  return;

              if (disposing)
              {
                  if (_dbContext != null)
                  {
                      _dbContext.Dispose();
                  }

                  // Free any other managed objects here.                    
              }

              // Free any unmanaged objects here.
              _disposed = true;
          }
      }

I am assuming under the hood, the DbContext is still using SqlCommand and SqlConnection to connect to the database.

I want to know when does EF Core close the SqlConnection? Does it close the connection as soon as it's done executing the query (like how we do in classic ADO) or when the DbContext is disposed?

So in the example above, will it dispose the connection before returning Orders from the GetActiveOrders() method or when the OrderService is disposed by the DI container?

user_5202563 · Answer

Side note: you shouldn't dispose something you don't own, which is the case with DI injected DbContext (and any injected object).

But to answer you concrete question. I can't find documentation to link to, but as soon as you

don't provide pre allocated DbConnection object via DbContextOptions
don't manually open connection via .Database.OpenConnection method
don't open explicit transactions via Database.BeginTransaction method

i.e. do not take some connection management by yourself (in which case you are responsible for closing/disposing), EF Core opens connection only when needed and closes it immediately after that.

When needed means opening a transaction during the SaveChanges until it gets commit or rolled back. Or when executing DbReader returning query until the returned reader is consumed or cancelled. In other words, exactly how you would do it with classic ADO.NET. And yes, behind the scenes it uses ADO.NET (at least for relational databases).

Some additional info here Do I need to close the DbConnection manually when using ambient transactions with EF Core 2.1? and Using EF Core to invoke stored procedure and closing connection. Also Connection Resiliency and How queries work documentation topics.

When does EF Core DBContext dispose Sql Connection?

1 Answers