How to avoid DbContext threading issues caused by frequent HttpRequests?

Question

I've taken over a code base from someone else. This is a web application built on Angular 8 (client) and .NET Core 3.0 (server).

Brief description of the application:

1. Frequent notifications are stored in a database, with an SqlTableDependency attached to it for detecting new notifications.
2. When new notifications occur, the server prompts all clients to request an updated list based on their custom filters. These client-to-server requests happen over HttpPost with the filter as a parameter.

The problem occurs when too many notifications arrive at once. Say, when 10 new notifications arrive, the server sends 10 update prompts to the client at the same time, causing the client to immediately send 10 HttpPost requests to the API.

The API takes the filter from the POST, uses it to query the database, and returns the filtered result to the calling client. However, when 10 of these arrive at the same time, it causes a DbContext error - more specific:

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.

public class AlarmController : Controller
{
    private readonly IAlarmRepository alarmRepo;
    private readonly ISiteRepository siteRepo;
    public AlarmController(IAlarmRepository alarmRepo, ISiteRepository siteRepo)
    {
        this.alarmRepo = alarmRepo;
        this.siteRepo = siteRepo;
    }

    [HttpPost("filter")]
    public async Task<IActionResult> FilterAlarm([FromBody] AlarmRequest alarmRequest)
    {
        var snmpReciverList = await this.alarmRepo.GetFilteredSNMPReceiverHistory(alarmRequest.FromDate, alarmRequest.ToDate);
        var siteList = await this.siteRepo.GetSiteListFiltered(int.Parse(alarmRequest.Filter), alarmRequest.SiteName);

        return Ok(await SNMPHistoryMapping.DoMapping(siteList, snmpReciverList);
    }

This HttpPost returns an Ok() with a list of the data requested, in which some mapping is done:

        IEnumerable<Site> sites = siteList;
        IEnumerable<SnmpreceiverHistory> histories = snmpReceiverList;

        IEnumerable<SNMPHistoryResponse> data = (from s in sites
                    join rh in histories on s.Address equals rh.Ipaddress
                    where priority > 0 ? s.SitePriority == priority : true
                    && !string.IsNullOrEmpty(trap) ? rh.AlarmDescription.Contains(trap) : true
                    select new SNMPHistoryResponse()
                    {
                        AlarmDescription = rh.AlarmDescription,
                        EventType = rh.EventType,
                        OnOffStatus = rh.OnOffStatus,
                        ParentSiteName = TraceFullParentDescription(s.Parent),
                        ReceiveTime = rh.ReceiveTime,
                        RepeatCount = rh.RepeatCount,
                        SiteName = s.Description,
                        SitePriority = s.SitePriority,
                        Status = AlarmStatus.GetStatusDescription(rh.EventType),
                        Value = rh.Value
                    });

When multiple of these [HttpPost("filter")] requests arrive at the same time, it appears as if a new thread is created for each one. They all connect on the same DbContext, and the next query starts before the previous is completed.

I can solve it by putting delays between each request from the client, but I want a more robust server-side solution to it, effectively processing these specific requests sequentially.

Note that this is EF Core and .NET Core 3.0, which does not have a SynchronizationContext.

Answer 1

I believe the comment posted by Panagiotis Kanavos is correct:

In this case a single DbContext is created by dependency injection, don't do that. All examples and tutorials show that the DbContexts are Scoped.

This catches me often, and actually just did. I wasn't using dependency injection, but sharing the DbContext around because I was being lazy. Best to properly set up dependency injection and do it the right way, e.g.:

IHostBuilder host = CreateHostBuilder(args);
host.ConfigureServices(services => {
    services.AddSingleton(service);
    // other stuff...

    // Then the context:
    services.AddScoped<DataContext>(x => {
        DbContextOptionsBuilder<DataContext> dbBuilder =
            new DbContextOptionsBuilder<DataContext>();
        dbBuilder.UseNpgsql(connstr);
        return new DataContext(dbBuilder.Options);
    });
});

// Start the host
host.Build().Run();

The documentation for AddScoped is here, and in true microsoft form, it is impossible to read or digest. Stackoverflow does a better job at explaining it.