EF 6 - How to correctly perform parallel queries

Question

When creating a report I have to execute 3 queries that involve separated entities of the same context. Because they are quite heavy ones I decided to use the .ToListAsync(); in order to have them run in parallel, but, to my surprise, I get a exception out of it...

What is the correct way to perform queries in parallel using EF 6? Should I manually start new Tasks?

Edit 1
The code is basically

using(var MyCtx = new MyCtx())
{
      var r1 = MyCtx.E1.Where(bla bla bla).ToListAsync();
      var r2 = MyCtx.E2.Where(ble ble ble).ToListAsync();
      var r3 = MyCtx.E3.Where(ble ble ble).ToListAsync();
      Task.WhenAll(r1,r2,r3);
      DoSomething(r1.Result, r2.Result, r3.Result);
}

Solution 1

The problem is this:

EF doesn't support processing multiple requests through the same DbContext object. If your second asynchronous request on the same DbContext instance starts before the first request finishes (and that's the whole point), you'll get an error message that your request is processing against an open DataReader.

Source: https://visualstudiomagazine.com/articles/2014/04/01/async-processing.aspx

You will need to modify your code to something like this:

async Task<List<E1Entity>> GetE1Data()
{
    using(var MyCtx = new MyCtx())
    {
         return await MyCtx.E1.Where(bla bla bla).ToListAsync();
    }
}

async Task<List<E2Entity>> GetE2Data()
{
    using(var MyCtx = new MyCtx())
    {
         return await MyCtx.E2.Where(bla bla bla).ToListAsync();
    }
}

async Task DoSomething()
{
    var t1 = GetE1Data();
    var t2 = GetE2Data();
    await Task.WhenAll(t1,t2);
    DoSomething(t1.Result, t2.Result);
}

Solution 2

As a matter of interest, when using EF Core with Oracle, multiple parallel operations like the post here using a single DB context work without issue (despite Microsoft's documentation). The limitation is in the Microsoft.EntityFrameworkCore.SqlServer.dll driver, and is not a generalized EF issue. The corresponding Oracle.EntityFrameworkCore.dll driver doesn't have this limitation.

Comments

[Eugene Podskal]

Possible duplicate of EF Data Context - Async/Await & Multithreading.

[Milton Filho]

A little tip. don't forget to call AsNoQueryable on your linq query. It will help with performance

[Peter Bons]

What exception do you get? can you share some code how you execute the queries in parallel?

[Leonardo]

@PeterBons check edit1... I get the invalid operation exception when starting the second query.

[Leonardo]

@MiltonFilho "AsNoQueryable"?!? that sounds highly doubtful... and google has no knowledge about that method... is that from a framework?

[Milton Filho]

Sorry the correct is AsNoTracking. It will tell to EF don't observe all properties from all objects and will run fast.

[Emil]

You could try using one MyCtx for each query.

[Josh]

Are there worthwhile performance gains when doing something like this?

[Peter Bons]

@Josh it depends. If multiple calls (let's say 10 calls) each take 1 sec than parallel execution can lead to shorter total duration that 10 seconds when run sequential. But a database engine has its limits as well. If you bombard it with parallel requests its resource consumption will go up.

[binki]

What if you want to have the loaded entities be all a part of a unit of work and make updates to them and commit them together in the end?

[mko]

This is an old answer but using this technique I am getting The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

[Peter Bons]

@mko you are probably relying on lazy loading, so EF tries to load additional data which fails due to the fact the context is disposed.

[Peter Bons]

@binki: you could separate the reads from the writes: read the entities, detach them from the context. Then modify them and attach them to a single context and save the changes.

[Gert Arnold]

This doesn't answer the question.

[cbp]

Be careful with mixing this approach and TransactionScope (if you are still using TransactionScope). You can get some funny behaviour. What is not obvious is that the two MyCtxs are instantiated immediately when GetE1Data() and GetE2Data() are called, not when Task.WhenAll is called. The ToListAsync() are executed in parallel. Can cause trouble.