Transient Database contexts from separate dependencies fails for parallel queries

Question

Background (TLDR: I need parallel queries)

I am building REST service that needs to be able to answer queries very fast. As such I'm pre-loading a large part of the database into memory and answering using that data instead of making complex database queries for each request. This works great, and the average response time of the API is well below the requirements and a lot faster than direct database queries.

But I have a problem. The service takes about 5 minutes to start and pre-load all of its information. During this time it can not answer queries.

Problem

I want to change this so that during the pre-load phase it makes database queries until the in-memory cache is loaded.

This leads me to a problem. I need to have multiple active queries to my database. Anyone who has tried this in EF Core has problably seen this message.

System.InvalidOperationException: A second operation started on this context before a previous operation completed. This is usually caused by different threads using the same instance of DbContext. For more information on how to avoid threading issues with DbContext, see https://go.microsoft.com/fwlink/?linkid=2097913.

The first sentence on the linked page is

Entity Framework Core does not support multiple parallel operations being run on the same DbContext instance.

I thought this would be easily solved by wrapping my cache-loading into its own class and the direct query into another, and then having both of these requiring their own instance of the Database Context. Then my service can in turn get these injected and use both of these dependencies in parallel.

This should be what I have:

I have also set up my database context so that it uses transient for all parts.

services.AddDbContext<IDataContext, DataContext>(options => 
  options.UseSqlServer(connectionString), ServiceLifetime.Transient, ServiceLifetime.Transient
);

I have also enabled MultipleActiveResultSets=True

All of this however results in the exact same error as listed above.

Again, everything is Transient except the HandlerService which is Singelton as I want this to keep a copy of the cache in memory and not have to load it for every request.

What is it I have failed to understand about the ef-core database context, or DI in general?

I figured out what the problem was. In my case there is as described above, one singleton handler. This handler has one (indirect) context (through DI) for fulfilling requests until the cache is loaded. When multiple parallel queries are sent to the API before the cache is loaded, then this error occurs as each of these request are using the same context. And in my test I was always hitting the parallel requests as part of the startup and hence the singelton service was trying to use the same db context for multiple requests. My solution is to in this one place step outside the "normal" dependency injection and use the IServiceScopeFactory to get a new instance of the dependency used to resolve requests before the cache is loaded. Bohdans answer led me to this conclusion and ultimate solution.

Answer 1

I'm not sure whether it qualifies for a full answer but it's too broad for a comment.

When doing .NET core background services which are obviously singletons too I use IServiceScopeFactory to create services with a limited lifetime.

Here's how I create a context

using (var scope = _scopeFactory.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<DbContext>();
}

My guess is that you could inject it in your hander and use it like this too. So it would allow you to leave context as scoped instead of transient with is default setting btw.

Hope that helps.