title: Reuse SqlConnection from DbContext
body: <p>I need to use a <code>SqlConnection</code> to perform an untyped native sql query using <code>SqlCommand.ExecuteReader()</code>. I have an existing DbContext instance available, which is used by some EF code inside the same transaction as the SqlConnection above. At the moment, I create a new SqlConnection, passing in the connection string from the DbContext:</p>
  
  <pre><code>using (var connection = new SqlConnection(db.Database.Connection.ConnectionString))
  </code></pre>
  
  <p>However, I dont think this is very efficient as it uses a second underlying connection to the DB, and because it is all in a transaction which involves a 2nd db connection (used by the DbContext EF code), it promotes the TX to using the DTC.</p>
  
  <p>I would like to make a <code>SqlConnection</code> that reuses the same underlying DB connection as that used by the DbContext EF code. This avoids creating / using a 2nd db connection and avoids the need for the DTC. Is this possible? Is this good practice?</p>
  
  <p>EDIT: I tried this code:</p>
  
  <pre><code>private List&lt;T&gt; UseReader&lt;T&gt;(DbContext db)
      {
          try
          {
              var connection = (db.Database.Connection) as SqlConnection;
  
              connection.EnlistTransaction(Transaction.Current);
  
              var command = connection.CreateCommand();
              ...
  </code></pre>
  
  <p>but it complained that the connection was not open. So then I changed the code to this:</p>
  
  <pre><code>    private List&lt;T&gt; UseReader&lt;T&gt;(DbContext db)
      {
          if( db.Database.Connection.State != ConnectionState.Open )
          {
              db.Database.Connection.Open();
          }
  
          try
          {
              var connection = (db.Database.Connection) as SqlConnection;
  
  
              connection.EnlistTransaction(Transaction.Current);
  
              var command = connection.CreateCommand();
              ..
  </code></pre>
  
  <p>And then I started getting this error:</p>
  
  <pre><code>System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---&gt; System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
  </code></pre>
  
  <p>So finally I tried this code:</p>
  
  <pre><code>    private List&lt;T&gt; UseReader&lt;T&gt;(DbContext db)
      {
          var opened = false;
  
          if( db.Database.Connection.State != ConnectionState.Open )
          {
              db.Database.Connection.Open();
              opened = true;
          }
  
          try
          {
              var connection = (db.Database.Connection) as SqlConnection;
  
  
              connection.EnlistTransaction(Transaction.Current);
  
              var command = connection.CreateCommand();
              ...
          }
          finally
          {
              if (opened)
              {
                  db.Database.Connection.Close();
              }
          }
  
  
      }
  </code></pre>
  
  <p>But that led to this error appearing in downstream EF code:</p>
  
  <pre><code>The operation cannot be completed because the `DbContext` has been disposed
  </code></pre>
  
  <p>EDIT 2: Solution - the final version of the code was correct actually. The reason I was received the error regarding the disposed <code>DbContext</code>, was because in our previous investigations we had included a <code>SqlConnection.ClearPool()</code> call somewhere downstream, which started throwing that error after including the <code>connection.close()</code> call.</p>