How DbContext will be provided with connection instance from Connection pool?

Question

We are using asp.net core 3.x with EF Core 3.x

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.

In order to achieve this we need to insert logged in user id and @@spid (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 DbInterceptor.

Session table:

userId	sessionId
1	32
2	26

Application table:

id	userId	text
1	1	I need help to ...
2	2	I don't speak english...

Db interceptor implementation:

public class DbInterceptor : DbCommandInterceptor
{
    private readonly IExecutionContext _executionContext;

    public DbInterceptor(IExecutionContext executionContext)
    {
        _executionContext = executionContext;
    }

    public override async Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command,
        CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        var sqlParameter = new SqlParameter("UserId",
            _executionContext.CurrentPrincipal.FindFirst(Claims.TSSUserId).Value);
        await eventData.Context.Database.ExecuteSqlRawAsync("EXEC InsertUserSP @UserId", sqlParameter);
        return await base.ReaderExecutingAsync(command, eventData, result);
    }

    public override async Task<DbDataReader> ReaderExecutedAsync(DbCommand command,
        CommandExecutedEventData eventData, DbDataReader result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        var sqlParameter = new SqlParameter("UserId",
            _executionContext.CurrentPrincipal.FindFirst(Claims.TSSUserId).Value);
        await eventData.Context.Database.ExecuteSqlRawAsync("EXEC DeleteUserSP @UserId", sqlParameter);

        return await base.ReaderExecutedAsync(command, eventData, result);
    }
}

Now with this we got an exception

System.InvalidOperationException: 'There is already an open DataReader associated with this Command which must be closed first.' on line await eventData.Context.Database.ExecuteSqlRawAsync("EXEC DeleteUserSP @UserId", sqlParameter); in `ReaderExecutedAsync` method of interceptor.

I googled this exception and found that this error can be overcome by providing MultipleActiveResultSets to true in connection string.

Is there any side effect of using MultipleActiveResultSets?

While goggling around that topic, I come across several articles stating that It may share connection instance among different request, when MultipleActiveResultSets 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 @@spid for all running live thread.

How DbContext will be provided with connection instance from Connection pool?

Answer 1

At ReaderExecutedAsync the data reader is still open and fetching rows. So it's too early to unset the user. Try hooking DataReaderDisposing instead.

If that doesn't work, force the connection open and call the procedure outside an interceptor. eg

var con = db.Database.GetDbConnection();
await con.OpenAsync();
await con.Database.ExecuteSqlRawAsync("EXEC InsertUserSP @UserId", sqlParameter);

This will ensure that the connection is not returned to the connection pool until the DbContext is Disposed.