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

Question

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?

This is all rather broad and unclear. Try to narrow it down to one example with one clear question. — Gert Arnold, May 19 '21 at 06:50
You're mixing up all kinds of concepts. Now you add connection pooling to them. MARS != pooling, MARS != multiple result sets (yes, really), MARS != parallel programming. Why don't you just load selected entities into the context serially and let relationship fixup do the job? Probably faster than any parallel database access followed by manual mapping. — Gert Arnold, May 19 '21 at 21:15
@GertArnold I'm sure I am as I'm not super familiar with these and looking for help. I want the most performant means to execute multiple queries and resolve to a `list` by taking advantage of any and all means. Connection pooling, MARS, async, etc. If you know or have a suggestion on what you think would be the most performant method, I'd love to see an answer to that effect. I'm not sure what you mean by "let relationship fixup do the job". I'm not sure what you mean by "parallel mapping followed by MANUAL mapping"? Why would it be manual? In my example, they are mapped to `TRESULT`. — crichavin, May 19 '21 at 23:20
I can't be less vague than you are. As a first try load all data sequentially into one context and you'll see that EF populates all references and collections. I.e. try to emulate [split queries](https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries) in EF core. — Gert Arnold, May 20 '21 at 09:00

Jonas Høgh · Answer 1 · 2021-05-19T07:56:01.830

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.

@crichavin misread your question as being about EF core, but a similar package is also available for EF 6, I've updated the link — Jonas Høgh, May 19 '21 at 07:57

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

1 Answers1