29

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);
}
Leonardo
  • 10,737
  • 10
  • 62
  • 155
  • Possible duplicate of [EF Data Context - Async/Await & Multithreading](http://stackoverflow.com/questions/20946677/ef-data-context-async-await-multithreading). – Eugene Podskal Jan 19 '17 at 19:20
  • A little tip. don't forget to call AsNoQueryable on your linq query. It will help with performance – Milton Filho Jan 19 '17 at 19:58
  • What exception do you get? can you share some code how you execute the queries in parallel? – Peter Bons Jan 19 '17 at 20:47
  • @PeterBons check edit1... I get the invalid operation exception when starting the second query. – Leonardo Jan 19 '17 at 22:14
  • 1
    @MiltonFilho "AsNoQueryable"?!? that sounds highly doubtful... and google has no knowledge about that method... is that from a framework? – Leonardo Jan 19 '17 at 22:16
  • 3
    Sorry the correct is AsNoTracking. It will tell to EF don't observe all properties from all objects and will run fast. – Milton Filho Jan 19 '17 at 22:52
  • You could try using one MyCtx for each query. – Emil Jan 19 '17 at 23:31

3 Answers3

40

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);
}
Leonel Sanches da Silva
  • 6,972
  • 9
  • 46
  • 66
Peter Bons
  • 26,826
  • 4
  • 50
  • 74
  • 3
    Are there worthwhile performance gains when doing something like this? – Josh Dec 10 '18 at 15:25
  • 7
    @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. – Peter Bons Dec 10 '18 at 15:36
  • 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? – binki Sep 12 '19 at 14:48
  • 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. – mko Sep 26 '19 at 11:28
  • 2
    @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 Sep 26 '19 at 11:39
  • 3
    @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. – Peter Bons Sep 26 '19 at 15:35
  • 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. – cbp May 12 '21 at 13:27
7

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.

retiredcoder
  • 71
  • 1
  • 3
-3

Check out https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

From the documentation:

Statement interleaving of SELECT and BULK INSERT statements is allowed. However, data manipulation language (DML) and data definition language (DDL) statements execute atomically.

Then your above code works and you get the performance benefits for reading data.

Siim Nelis
  • 882
  • 7
  • 10