title: How DbContext will be provided with connection instance from Connection pool?
body: <p>We are using asp.net core 3.x with EF Core 3.x</p>
  <p>We do have authorization on couple of entities(so that it only allow few of the records from table returned as response) which is achieved by accessing SQL view (internally joins two table) and we query against that view which will give you only those records which logged in user is authorized to.</p>
  <p>In order to achieve this we need to insert logged in user id and <code>@@spid</code> (SQL Server) to the table (Session) (being used in above view) just before executing select queries (on Application table) and we need to delete that record immediately after query is executed. In order to achieve this we are using <code>DbInterceptor</code>.</p>
  <p>Session table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>userId</th>
  <th>sessionId</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>32</td>
  </tr>
  <tr>
  <td>2</td>
  <td>26</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Application table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>userId</th>
  <th>text</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>I need help to ...</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>I don't speak english...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Db interceptor implementation:</p>
  <pre class="lang-cs prettyprint-override"><code>public class DbInterceptor : DbCommandInterceptor
  {
      private readonly IExecutionContext _executionContext;
  
      public DbInterceptor(IExecutionContext executionContext)
      {
          _executionContext = executionContext;
      }
  
      public override async Task&lt;InterceptionResult&lt;DbDataReader&gt;&gt; ReaderExecutingAsync(DbCommand command,
          CommandEventData eventData, InterceptionResult&lt;DbDataReader&gt; result,
          CancellationToken cancellationToken = new CancellationToken())
      {
          var sqlParameter = new SqlParameter(&quot;UserId&quot;,
              _executionContext.CurrentPrincipal.FindFirst(Claims.TSSUserId).Value);
          await eventData.Context.Database.ExecuteSqlRawAsync(&quot;EXEC InsertUserSP @UserId&quot;, sqlParameter);
          return await base.ReaderExecutingAsync(command, eventData, result);
      }
  
      public override async Task&lt;DbDataReader&gt; ReaderExecutedAsync(DbCommand command,
          CommandExecutedEventData eventData, DbDataReader result,
          CancellationToken cancellationToken = new CancellationToken())
      {
          var sqlParameter = new SqlParameter(&quot;UserId&quot;,
              _executionContext.CurrentPrincipal.FindFirst(Claims.TSSUserId).Value);
          await eventData.Context.Database.ExecuteSqlRawAsync(&quot;EXEC DeleteUserSP @UserId&quot;, sqlParameter);
  
          return await base.ReaderExecutedAsync(command, eventData, result);
      }
  }
  </code></pre>
  <p>Now with this we got an exception</p>
  <blockquote>
  <p>System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.' on line await eventData.Context.Database.ExecuteSqlRawAsync(&quot;EXEC DeleteUserSP @UserId&quot;, sqlParameter); in `ReaderExecutedAsync` method of interceptor.</p>
  </blockquote>
  <p>I googled this exception and found that this error can be overcome by providing <code>MultipleActiveResultSets</code> to true in connection string.</p>
  <p>Is there any side effect of using <code>MultipleActiveResultSets</code>?</p>
  <p>While goggling around that topic, I come across several articles stating that It may share connection instance among different request, when <code>MultipleActiveResultSets</code> is set to true. If same connection is shared among the live request threads, then it can be problematic since authorization is working on the fact that it will have unique <code>@@spid</code> for all running live thread.</p>
  <p>How <code>DbContext</code> will be provided with connection instance from Connection pool?</p>