EF Data Context - Async/Await & Multithreading

Question

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?

score 69 · Answer 1 · answered Jan 08 '14 at 12:04

69

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.

answered Jan 08 '14 at 12:04

Stephen Cleary

437,863
77
675
810

Thanks for clarifying this. Is there a source where we could read about thread safety improvements in EF6? I could only find [this piece](http://msdn.microsoft.com/en-us/library/system.data.entity.dbcontext%28v=vs.113%29.aspx) so far: *Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.* – noseratio Jan 08 '14 at 12:35
1

I wasn't able to find anything specific, but the [example they provide](http://msdn.microsoft.com/en-us/data/jj819165.aspx) does jump threads at `await`. They did state that they are [*not* making it fully threadsafe](https://entityframework.codeplex.com/wikipage?title=Task-based%20Asynchronous%20Pattern%20support%20in%20EF.#ThreadSafety). – Stephen Cleary Jan 08 '14 at 12:37
1

@Noseratio Wow, so you can't wait for two pending SQL requests to complete using `Task.WhenAll`? I didn't tried using async EF yet, but this seems a bit disappointing to me. – ken2k Jan 08 '14 at 13:53
@ken2k, sorry I deleted my last comment as redundant, but yes, that's what they're saying [here](https://entityframework.codeplex.com/wikipage?title=Task-based%20Asynchronous%20Pattern%20support%20in%20EF.#ThreadSafety). Although, perhaps that applies to a single instance of (say) `DbContext` object. – noseratio Jan 08 '14 at 13:57
3

@Noseratio So the unique point in using async API with EF is to have more scalability for long running requests (as you don't need to block a thread in the threadpool waiting for the request to complete). That's just _one_ of the two important advantages of async/await, i.e. scalability AND performance. If you can't improve performance by querying multiple requests at the same time (SQL Server would handle this just perfectly), it's very disappointing. Thanks for all the questions/answers about this subject anyway, that's very interesting. – ken2k Jan 08 '14 at 14:03
9

I believe you can do multiple simultaneous requests, as long as they each have their own `DbContext`. – Stephen Cleary Jan 09 '14 at 00:10
So currently is there is any way/pattern to use it with thread safty in .Net MVC WebAPI? – Anshul Nigam May 26 '14 at 06:25
1

@AnshulNigam: If by "it", you mean `DbContext`, then you just ensure you're targeting .NET 4.5 and use `async`/`await`, and it'll work fine. – Stephen Cleary May 26 '14 at 13:59
@StephenCleary could be some way to replace `Task.WhenAll` (When the tasks share the same `DbContext`)? Maybe could I iterate over the collection of tasks and execute `await Task.Run(() => myTask);` ? – sabotero Sep 12 '16 at 10:03
@sabotero: By the time the code hits `WhenAll`, it's too late - each task is already in progress. You'll need to just `await` each task individually right away instead of passing them to `WhenAll`. – Stephen Cleary Sep 12 '16 at 12:50
@StephenCleary I see, so rather than calling `await Task.WhenAll(myTaskCollection)` I have to iterate over my collection of tasks and execute `await Task.Run(()=> myTask)` in the body of the `foreach` for exemple – sabotero Sep 12 '16 at 16:01
@sabotero: No. You need to `await` them as you get them. There shouldn't be a `myTaskCollection` at all, nor is there any need for `Task.Run`. If this is unclear, I recommend you ask your own question, where the code formatting is much nicer. – Stephen Cleary Sep 12 '16 at 16:43
@StephenCleary so something like this is not good: `var accountsDbTasks = myDbSet.Where(filter).Select(async a => new Account{ ItemId = await EnsureItemDbId(finexkapCompanyId, a.Item), Resource = await EnsureResource(a.ResourceType, a.ResourceUri), BankId = await EnsureBankDbId(a.Item.Bank) }).ToList();` – sabotero Sep 13 '16 at 06:50
@sabotero: I doubt passing an `async` delegate to `Select` would work with any db provider. – Stephen Cleary Sep 13 '16 at 11:12
1

I'd just like to add I recently made a large project with the service layer wrapping the EF layer and every function using its own context. I made everything in the service layer async using the .tolistasync and related methods. I ended up having to scrap things pretty far down the line because once I started using heavy multithreading (WPF, lots of async/await logic) I would start getting random issues way down the call stack related to manipulating collections with multiple threads in code internal to EF. I can say with certainty the this answer is NOT CORRECT. – Paul Swetz Jul 18 '18 at 15:14

score 43 · Accepted Answer · edited Oct 08 '18 at 21:33

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.

That's really disappointing. The *one* element where web applications could have real gains from asynchronicity are database calls... but that's not supported w/ EF. — Alex, Jan 06 '14 at 22:57
"on a different thread from the one the object was originally created on, and this is not something DataContext would expect." Can you please explain, does DbContext remembers the thread that created it? What exactly is the problem? — alpha-mouse, Jun 17 '15 at 10:32
@alpha-mouse, AFAIR without diving into [the sources](http://referencesource.microsoft.com/#System.Data.Linq/DataContext.cs,c5dfdc18214096bb), it depends on some helper classes which in turn use thread local storage (TLS). This might have changed in the recent EF versions. — noseratio, Jun 17 '15 at 17:34
For everyone looking at this, using did fix the problem for me, see wertzui's answer here: http://stackoverflow.com/questions/35847807/async-method-that-includes-async-call-and-sync-action-on-result/35848099#35848099 — VSO, Mar 07 '16 at 16:28
where does the option TransactionScopeAsyncFlowOption.Enabled fit into this? is that relevant at all when using a DataContext? — Simon_Weaver, Jan 17 '17 at 04:44
@Simon_Weaver, `TransactionScopeAsyncFlowOption` would solve a similar problem for `TransactionScope`, but AFAIK it has nothing to do with `DataContext`. Note the OP has edited the question and replaced `DataContext` with `DbContext`, and `DbContext` is already `async` friendly (but not cuncurrency-frienldy, see Stephen's answer). — noseratio, Jan 17 '17 at 07:01
@Noseratio thanks. I just cheated for now and removed the sync but yes I meant DbContext. Trying to optimize some ancient Linq2Sql — Simon_Weaver, Jan 17 '17 at 07:03

score -3 · Answer 3 · edited Sep 30 '18 at 06:29

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

Not an answer to the question. – trenthaynes May 01 '19 at 19:32 — trenthaynes, May 01 '19 at 19:32

EF Data Context - Async/Await & Multithreading

3 Answers3

Linked