Efcore: GetDbConnection() and connection pool management

[bragma]

Hi, I've seen many uses of GetDbConnection() wrapped in a using block. Since disposing the returned connection closes it, I am expecting this is required for some resource management in the connection pool but I've found no explicit explanation of what GetDbConnection does internally (ex: increment a reference counter) and if the "using" is really required in case DI is used for managing the (scoped) lifecycle of the DbContext.

Ex:
In an ASP NET Core app I interweave some use of EF and Dapper and I am in doubt on how to use GetDbConnection(). The pattern I've found on the net is:

using (var conn = _dbContext.Database.GetDbConnection())
{
    if (conn.State != ConnectionState.Open)
    {
        await conn.OpenAsync();
    }

    // Dapper query here
}

But this causes problems since after the "using" EF does not work anymore (the connection is closed) in the same scope (asp next controller). I was considering if the using can be removed and still not cause any connection leak:

var conn = _dbContext.Database.GetDbConnection();
if (conn.State != ConnectionState.Open)
{
    await conn.OpenAsync();
}

// Dapper query here

i.e., can the using be safely removed and still expect connection to be properly handled when the asp net controller method using the DbContext has completed?
Thanks!

[ajcvickers]

@bragma If EF creates the DbConnection object, then EF will ensure it is disposed when the DbContext is disposed. On the other hand, if some other code creates the DbConnection object and passes it to EF, then it is the responsibility of the other code to also dispose the connection appropriately.

Similar rules apply to opening and closing the connection. If EF opens the connection, then EF will close the connection when it is done with it. If your code opens the connection, then your code should close the connection.

[ajcvickers]

@bragma Yes, although it would be considered best practice to pair an explicit open and close.

[bragma]

Sorry, I have mistakenly deleted my second question. As a reference, I am adding it back:

Given that DI will dispose DbContext and this in turn will dispose the EF created DbConnection in it, if the DbConnection is obtained via GetDbConnection() can we assume that even if it opened explicitly with conn.open() it will be closed automatically when DbConnection is disposed (so in this case when DbContext will be disposed)?

Answer by @ajcvickers is:

"Yes, although it would be considered best practice to pair an explicit open and close."

Thanks a lot!

[peteralbanese]

I was having the same issue as above and changed my code to the following. When the context.Database.GetDbConnection() is called in a Using Statement, it works the first time, but all subsequent calls to cn.Close on that Connection will fail. So I modified my code to NOT use the Using Statement and it worked. Hope this helps someone.

public static DataTable GetDataTable (this DbContext context, string sql, Dictionary parameters)
{

        DataTable dt = new DataTable();
        DbConnection cn = context.Database.GetDbConnection();

        using (DbCommand cmd = cn.CreateCommand())
        {
            cmd.CommandText = sql;
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    DbParameter dbParam = cmd.CreateParameter();
                    dbParam.ParameterName = parameter.Key;
                    dbParam.Value = parameter.Value;
                    cmd.Parameters.Add(dbParam);
                }
                if (cn.State.Equals(ConnectionState.Closed)) { cn.Open(); }
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }
        }

        if (cn.State.Equals(ConnectionState.Open)) { cn.Close(); }

        return dt;
    }

[rudrikandharia]

I have the same scenario. DbConnection is obtained via GetDbConnection() and it is opened explicitly with conn.open(). But I think DI does not dispose my DbConnection properly.
I'm using .net core 2.2.
In Startup, context is added with default method, so context has 'Scoped' lifetime.
services.AddDbContext<Context>();
I think It should be disposed after a request is served, but it is not.
Why Do I think so,
My connection creates some global temporary tables, Which I can see in System Databases -> tempdb -> TemporaryTables.
After request is served. These tables are still there. Shouldn't they get deleted automatically?

[ajcvickers]

@Rudrik-Andharia Please open a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[lauxjpn]

@bragma If EF creates the DbConnection object, then EF will ensure it is disposed when the DbContext is disposed. On the other hand, if some other code creates the DbConnection object and passes it to EF, then it is the responsibility of the other code to also dispose the connection appropriately.

Similar rules apply to opening and closing the connection. If EF opens the connection, then EF will close the connection when it is done with it. If your code opens the connection, then your code should close the connection.

This should probably make it in the docs (an article about reusing a DbContext connection and related stuff might be helpful), or at least in the RelationalDatabaseFacadeExtensions.GetDbConnection(DatabaseFacade) API reference.