Does Entity Framework support parallel async queries? [duplicate]

Question

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);
    }
}

Chemash

See #41750396 – Seminole

Answer 1

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.

Eupheemia

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! 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. – Inocenciainoculable

@ChrisMoschini I'm pretty sure the last sentence is not in refernce to EF but to the SQL Server. – Anthonyanthophore

@ErikPhilips Not sure what you mean, can you clarify? – Inocenciainoculable

What is the point of using .ToListAsync() as oppose to ,ToList() if you can't run them asynchronously? – Transgress

@Transgress 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 stackoverflow.com/a/12673868 – Eupheemia

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. – Crying

@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. – Eupheemia

Answer 2

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.

Indulge

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. – Chemash

I can't give a source all I can say is that I have code running in production that uses that method. – Indulge

Fingers crossed your pager will not go off at night. Thanks for reporting this. – Chemash

A context is not thread-safe, never. The database provider doesn't have any influence. This is plainly wrong information. – Terryn

Answer 3

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.

Dance

This is not related to parallel processing and therefore not relevant to mention here. – Terryn