63

What happens when we start multiple async Entity Framework queries and run them in parallel?

Are they physically executed in parallel? Are they serialized by Entity Framework? Is this unsupported? Does it result in an exception?

public async Task QueryDatabase()
{
    using (var context = new MyDbContext())
    {
        Task task1 = context.SomeTable1.ToListAsync();
        Task task2 = context.SomeTable2.ToListAsync();

        await Task.WhenAll(task1, task2);
    }
}
usr
  • 168,620
  • 35
  • 240
  • 369

3 Answers3

73

This is not supported as per the specifications of version 6.

This should throw a DbConcurrencyException exception saying

A second operation started on this context before a previous asynchronous operation completed. Use 'await' to ensure that any asynchronous operations have completed before calling another method on this context. Any instance members are not guaranteed to be thread safe.

EF will detect if the developer attempts to execute two async operations at one time and throw.

From a codeplex page of the project:

Enabling asynchronous execution of database operations is actually orthogonal to enabling concurrent execution on the same context. In the particular case of server scenarios, using concurrent access could affect scalability negatively as it would mean that in order to process a single request you would be spinning of an arbitrary number of different threads. All the threads would compete for resources such as memory with other threads necessary to server other concurrent requests.

Entity Framework Core does not support this scenario either.

EF Core doesn't support multiple parallel operations being run on the same context instance. You should always wait for an operation to complete before beginning the next operation. This is typically done by using the await keyword on each async operation.

Community
  • 1
  • 1
ken2k
  • 48,145
  • 10
  • 116
  • 176
  • 7
    Upvote for accuracy, although I wish I could downvote the guy who wrote the Codeplex snippet. A good query is almost entirely a hit to the db, which in most cases is multi-CPU and in many cases networked. Throwing a bunch of parallel async calls to the db uses NO threads! http://blog.stephencleary.com/2013/11/there-is-no-thread.html It should be trivial for EF to support this if all queries are .AsNoTracking(), but it still throws this exception. – Chris Moschini Jan 29 '15 at 23:18
  • @ChrisMoschini I'm pretty sure the last sentence is not in refernce to EF but to the SQL Server. – Erik Philips Aug 07 '15 at 03:09
  • @ErikPhilips Not sure what you mean, can you clarify? – Chris Moschini Aug 07 '15 at 18:47
  • What is the point of using .ToListAsync() as oppose to ,ToList() if you can't run them asynchronously? – MIKE Nov 20 '15 at 03:00
  • 5
    @MIKE Well it **is** asynchronous ; it's just you can't run multiple asynchronous SQL operations in parallel. Asynchronous programming has multiple advantages on the server side: the first is _not_ better performances, it's _scalability_, which means you won't block a thread until the operation completion. Even if you can't run two SQL requests in parallel, it's still a great advantage. See for instance http://stackoverflow.com/a/12673868 – ken2k Nov 20 '15 at 09:15
  • This is old, but the keyword is "operations", not necessarily "queries". Only things that are atomic (inserts, updates, deletes) cannot be run in parallel, because it can lead to inconsistent state. However selects, counts, etc. are 100% fine to be run in parallel. – Chris Pratt Jul 26 '19 at 17:31
  • 1
    @ChrisPratt In EF6, even queries cannot run in parallel. You just _cannot_ start multiple async operations (get, count, update, delete, insert...) at the same time. – ken2k Jul 30 '19 at 12:31
5

Just a note, as mentioned by ken2k this is not allowed when using Entity Framework with MS SQL Server. However, if you are using Entity Framework with Oracle, this is allowed.

jkruer01
  • 2,175
  • 4
  • 32
  • 57
  • 6
    Can you give a source for that? My impression was that EF internally is not capable of running multiple queries at the same time. This would require its internal structures such as the identity map to be thread safe. – usr Sep 19 '16 at 17:15
  • 2
    I can't give a source all I can say is that I have code running in production that uses that method. – jkruer01 Sep 19 '16 at 17:17
  • 7
    Fingers crossed your pager will not go off at night. Thanks for reporting this. – usr Sep 19 '16 at 17:57
  • A context is not thread-safe, never. The database provider doesn't have any influence. This is plainly wrong information. – Gert Arnold Mar 31 '21 at 10:37
-1

Just wanted to update that with EF 5 it's now possible to split queries that load navigation properties in separate queries: https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries

Still not solving your case but might be useful to know.

Ali Bdeir
  • 4,151
  • 10
  • 57
  • 117
user1589652
  • 7
  • 2