EF Core error: The connection does not support MultipleActiveResultSets

Question

I search thru all the links which had solution for the error. But, none of them were applicable for me as I had asynchronous code already and doing everything they have suggested.

We have Azure Functions based on .NET Core 3.1. We use latest version of Entity Framework Core. We are intermittently getting this error:

System.InvalidOperationException: The connection does not support MultipleActiveResultSets.

at Microsoft.Data.SqlClient.SqlCommand.<>c.b__164_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
End of stack trace from previous location where exception was thrown

at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
End of stack trace from previous location where exception was thrown ---

at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.AsyncEnumerator.MoveNextAsync() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)

When we looked at the logs in AppInsights, we found that the exceptions occurred at the same time with exact same error for same function at the same place. But, it was for three different invocations (InvocationId) but same host instance (HostInstanceId) and different operation Id (Operation ID). Expectation is that for every new invocation, new dbContext will be instantiated as the AddDbContextPool adds scoped dbContext as dependency by default. Not sure if we can deduct anything out of it.

Below is our implementation approach. Appreciate any help on this. Thanking in advance.

We add DbContext to the services using following statement in the startup file:

builder.Services.AddDbContextPool<OurDbContext>(options =>
{
    options.UseSqlServer("connectionstring"), builder =>
    {
       builder.EnableRetryOnFailure(3, TimeSpan.FromSeconds(2), null);
    });
});

OurDbContext class has the following constructor:

public OurDbContext(DbContextOptions<OurDbContext> options)
    : base(options)
{
}

And then we inject OurDbContext class in different repositories which uses this context to talk to SQL. Similar to below:

public class TypesRepo : RepoBase<Types>, ITypesRepo
{
  public TypesRepo(OurDbContext ourDbContext) : base(ourDbContext)
  {
  }
  
  public async Task RetrieveTypesAsync(List<string> types)
  {
    var records = await RetrieveConditionAsync(x => types.Contains(x.Type));
    return records?.Select(x => new { x.Type, x.TypeId })
                   .ToDictionary(x => x.Type, x => x.TypeId);
  }
}

public abstract class RepoBase<T> where T : class
{
    protected OurDbContext OurDbContext  { get; set; }

    public RepoBase(OurDbContext OurDbContext)
    {
        this.OurDbContext = OurDbContext;
    }

    public async Task<List<T>> RetrieveConditionAsync(Expression<Func<T, bool>> expression)
    {
        return await OurDbContext.Set<T>().Where(expression).AsNoTracking().ToListAsync();
    }
}

We inject above Repo class in Function classes and call above methods such as

await _typesRepo.RetrieveAsync()

P.S.: Based on below comment
I think dbcontextpool will reuse the dbcontext instance if it the connection is not active/unused but not the one which is active.

AddDbContext or AddDbContextPool

Answer 1

Your connection string needs to specify MultipleActiveRecordSets=true; to tell SqlServer to make sure this feature is enabled on the connections you make to it.

For example:

Data Source=localhost;Initial Catalog=master;Integrated Security=True;MultipleActiveResultSets=True

More info here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

Answer 2

We recently ran into a similar issue, and it was due to having a request spin up multiple threads simultaneously, and each thread was using the same DbContext to parallelize a heavy database query.

Further, we use DbReader for working with the results, and that locks up the connection until the reader is disposed of.

That prevents the other threads, which are re-using the same DbContext / connection instance, which also are using DbReader to process results.

Enabling the multiple-active result sets feature solves this for us, and in our case, we actually do not notice a performance hit at all with this.