CODEWITHSUNDEEP
entity-frameworkasynchronousentity-framework-6sql-server-mars
crichavin
crichavin

Reputation: 4582

Does Entity Framework 6 async parallel queries use Multiple Active Result Sets for connection pooling

I have some very large queries the EF is creating resulting in slow response times and high CPU use so I thought as a way to optimize I'd try to implement MARS and Async parallel queries to pull back multiple, simpler result sets in parallel and manipulating in memory.

i.e. I'd like to do this:

public async Task<IEnumerable<TResult>> GetResult<TResult>()
{
    using(var context = new Context())
    {
        return await context.Set<TResult1>().ToListAsync().ConfigureAwait(false);
    }
}

 
IEnumerable<TResult1> result1;
IEnumerable<TResult2> result2;

var result1Task = GetResult<TResult1>();
var result2Task = GetResult<TResult2>();

await Task.WhenAll(result1Task, result2Task).ConfigureAwait(false);

var result1 = result1Task.Result;
var result2 = result2Task.Result;

But not sure if this takes advantage of connection pooling since it creates a new DBContext for each task.

I found this article, but it isn't using Entity Framework.

I found this one using Core and it wasn't a recommended strategy.

And this one using Entity Framework for .NET framework, but is using a stored procedure as the example, but I just want to issue say 3 read queries in parallel, not call an SP.

Ideally looking for a way to achieve multiple result sets using linq to generate the SQL (vs using strings select Id, VendorName From Vendors....) and auto mapping the results to a class without having to use strings (vendorID = (int)vendorReader["BusinessEntityID"];).

Is this possible or a pipe dream?

Upvotes: 2

Views: 1402

Answers (1)

Jonas H&#248;gh
Jonas H&#248;gh

Reputation: 10874

The requirement for running multiple queries concurrently is usually not solved in ORMs with parallelism in the application. It is not safe to access a single DbContext from multiple threads. Instead, a pattern known as future queries is used. For EF6 this is available in the third party library https://www.nuget.org/packages/Z.EntityFramework.Plus.EF6/

The API is very simple and consists of an extension method that will cause the queries to be added to an internal list until the time when one of the queries is materialized (e.g. by calling ToList). At this time, all the queries are sent to the server in a single batch, and the results are returned together as well.

Upvotes: 2

Related Questions