Doug.Instance

I’m working on upgrading a framework to dotnet core so I am moving from .Net 2.x conventions to netstandard 2.2. Our code was using DbContext.Database.Connection to get DB connections for custom SQL. I needed to switch to DbContext.Database.GetDbConnection(). I made the wrong assumption that GetDbConnection() was a factory method and returned a new connection every time. Therefore I made sure I was disposing of each connection. Tests immediately started failing with “System.InvalidOperationException: ‘The ConnectionString property has not been initialized.'” After investing way too much time due to the complexity of the framework and my own stubbornness, I narrowed the issue down to the following scenario:

using (var conn = context.Database.GetDbConnection())
{
  conn.Open();
  using (var cmd = conn.CreateCommand())
  {
    cmd.CommandText = "SELECT * FROM sys.databases";
    cmd.ExecuteNonQuery();
  }
}
 
using (var conn = context.Database.GetDbConnection())
{
  conn.Open();
  using (var cmd = conn.CreateCommand())
  {
    cmd.CommandText = "SELECT * FROM sys.databases";
    cmd.ExecuteNonQuery();
  }
}

The real issue is the second call to GetDbConnection(). This does not in fact return a new instance, it appears to return the previous connection and the ConnectionString property has been set to an empty string causing the exception about ConnectionString not being initialized. You can test this yourself with the following:

var conn2 = context.Database.GetDbConnection();
Console.WriteLine(conn2.ConnectionString);
conn2.Dispose();
Console.WriteLine(conn2.ConnectionString);

The fix is to simply not dispose of your connections or commands. As indicated in this issue comment, disposing of the context will dispose of any connections created using GetDbConnection(). Therefore the correct implementation of this use case is as follows:

using (var context = new MyContext())
{
  var conn = context.Database.GetDbConnection();
  conn.Open();
  var cmd = conn.CreateCommand();
  cmd.CommandText = "SELECT * FROM sys.databases";
  cmd.ExecuteNonQuery();
  conn.Close();
 
  var conn2 = context.Database.GetDbConnection();
  conn2.Open();
  cmd = conn2.CreateCommand();
  cmd.CommandText = "SELECT * FROM sys.databases";
  cmd.ExecuteNonQuery();
}