EF Data Context - Async/Await & Multithreading

I frequently use async/await to ensure ASP.NET MVC Web API threads are not blocked by longer-running I/O and network operations, specifically database calls.

The System.Data.Entity namespace provides a variety of helper extensions here, such as FirstOrDefaultAsync, ContainsAsync, CountAsync and so forth.

However, since data contexts are not thread safe, this means that the following code is problematic:

var dbContext = new DbContext();
var something = await dbContext.someEntities.FirstOrDefaultAsync(e => e.Id == 1);
var morething = await dbContext.someEntities.FirstOrDefaultAsync(e => e.Id == 2);

In fact, I'm sometimes seeing exceptions such as:

>System.InvalidOperationException: >The connection was not closed. The connection's current state is open.

Is the correct pattern then to use a separate using(new DbContext...) block for each asynchronous call to the database? Is it potentially more beneficial to just execute synchronous then instead?

The DataContext class is part of LINQ to SQL. It does not understand async/await AFAIK, and should not be used with the Entity Framework async extension methods.

The DbContext class will work fine with async as long as you are using EF6 or higher; however, you can only have one operation (sync or async) per DbContext instance running at a time. If your code is actually using DbContext, then examine the call stack of your exception and check for any concurrent usage (e.g., Task.WhenAll).

If you are sure that all access is sequential, then please post a minimal repro and/or report it as a bug to Microsoft Connect.

We have a stalemate situation here. AspNetSynchronizationContext, which is responsible for the threading model of an ASP.NET Web API execution environment, does not guarantee that asynchronous continuation after await will take place on the same thread. The whole idea of this is to make ASP.NET apps more scalable, so less threads from ThreadPool are blocked with pending synchronous operations.

However, the DataContext class (part of LINQ to SQL ) is not thread-safe, so it shouldn't be used where a thread switch may potentially occurr across DataContext API calls. A separate using construct per asynchronous call will not help, either:

var something;
using (var dataContext = new DataContext())
{
    something = await dataContext.someEntities.FirstOrDefaultAsync(e => e.Id == 1);
}

That's because DataContext.Dispose might be executed on a different thread from the one the object was originally created on, and this is not something DataContext would expect.

If you like to stick with the DataContext API, calling it synchronously appears to be the only feasible option. I'm not sure if that statement should be extended to the whole EF API, but I suppose any child objects created with DataContext API are probably not thread-safe, either. Thus, in ASP.NET their using scope should be limited to that of between two adjacent await calls.

It might be tempting to offload a bunch of synchronous DataContext calls to a separate thread with await Task.Run(() => { /* do DataContext stuff here */ }). However, that'd be a known anti-pattern, especially in the context of ASP.NET where it might only hurt performance and scalability, as it would not reduce the number of threads required to fulfill the request.

Unfortunately, while the asynchronous architecture of ASP.NET is great, it remains being incompatible with some established APIs and patterns (e.g., here is a similar case). That's especially sad, because we're not dealing with concurrent API access here, i.e. no more than one thread is trying to access a DataContext object at the same time.

Hopefully, Microsoft will address that in the future versions of the Framework.

[UPDATE] On a large scale though, it might be possible to offload the EF logic to a separate process (run as a WCF service) which would provide a thread-safe async API to the ASP.NET client logic. Such process can be orchestrated with a custom synchronization context as an event machine, similar to Node.js. It may even run a pool of Node.js-like apartments, each apartment maintaining the thread affinity for EF objects. That would allow to still benefit from the async EF API.

[UPDATE] Here is some attempt to find a solution to this problem.

Asynchronous programming is a means of parallel programming in which a unit of work runs separately from the main application thread and notifies the calling thread of its completion, failure or progress. The main benefits one can gain from using asynchronous programming are improved application performance and responsiveness.

Entity Framework 6.0 supports asynchronous Programming, it means asynchronously you can query data and save data. By using async/await you can easily write the asynchronous programming for entity framework.

Example:

public async Task<Project> GetProjectAsync(string name) 
{
    DBContext _localdb = new DBContext();
    return await _localdb.Projects.FirstOrDefaultAsync(x => x.Name == name);
}

https://rajeevdotnet.blogspot.com/2018/06/entity-framework-asynchronous.html