Async/Await Multiple CountAsync Database Calls EFCore

Question

I am trying to make sure I understand async/await. In the following example, will my code run asynchronously or synchronously?

My understanding, which may be wrong, is that each async database call will not have to wait on the previous call to finish. So, I could essentially run numerous CountAsync calls, and they would all run at the same time until at which point something tries to get data from one of the async calls.

Here is what I currently have: (All the select/where logic has been removed because it's just not needed for this question)

public async Task<DashboardModel> GetDashboard(DashboardInput input)
    {
        DashboardModel model = new DashboardModel();
        model.MyCustomers = await _context.Customers.Where(x => [...]).Select(x => new DashboardCustomerModel()
        {
            [...]
        }).ToListAsync();

        model.TotalCustomers = await _context.Customers.CountAsync(x => [...]);

        model.MyTotalCustomers = await _context.Customers.CountAsync(x => [...]);
        model.MyClosedCustomers = await _context.Customers.CountAsync(x => [...]);
        model.MyNotStartedCustomers = await _context.Customers.CountAsync(x => [...]);

        model.OtherTotalCustomers = await _context.Customers.CountAsync(x => [...]);
        model.OtherClosedCustomers = await _context.Customers.CountAsync(x => [...]);
        model.OtherNotStartedCustomers = await _context.Customers.CountAsync(x => [...]);

        model.PreparerApprovedCustomers = await _context.Customers.CountAsync(x => [...]);
        model.ReviewerApprovedCustomers = await _context.Customers.CountAsync(x => [...]);
        model.ApprovedCustomers = await _context.Customers.CountAsync(x => [...]);

        return model;
    }

My colleague states that this is not correct and all the calls will run synchronously; Hence the reason I am asking this question. If I am wrong then what is the proper way to write this method so that all the async calls run at the same time?

You appear to be confusing "[asynchronously](https://stackoverflow.com/q/37419572/11683)" and "in parallel". Your queries will run asynchronously and sequentially, one after another. That is what `await` does. If you want async tasks to run in parallel, see https://stackoverflow.com/q/19431494/11683. But you [cannot do that with EF](https://stackoverflow.com/q/24702183/11683). — GSerg, Jul 26 '19 at 17:08
If you want the calls to run in parallel then collect the resulting Tasks into a collection then use `Task.WhenAll` to await them. — juharr, Jul 26 '19 at 17:11
@GSerg: You actually can do that with EF, it's just not *safe* to do so in all circumstances. For example, there's no support for attempting multiple asynchronous updates, so the queries would just hit the database as they hit, and could lead to weird or incorrect behavior in some circumstances. However, something like running a count is 100% perfectly fine to done asynchronously, as it has no impact on the data. — Chris Pratt, Jul 26 '19 at 17:24

score 1 · Answer 1 · answered Jul 26 '19 at 17:21

1

Tasks return hot, or already started. The await keyword quite literally means stop here until the task completes. So, yes, with the code you have, each query will run serially, in order, one at a time, as you proceed and then stop on each line.

To run in parallel, you need only just start the tasks. For example:

var totalCustomersTask = _context.Customers.CountAsync(x => [...]);

var myTotalCustomersTask = _context.Customers.CountAsync(x => [...]);
var myClosedCustomers = _context.Customers.CountAsync(x => [...]);
var myNotStartedCustomers = _context.Customers.CountAsync(x => [...]);

...

Notice that there's no await on any of these lines. Then, after you've kicked off all the tasks:

model.TotalCustomers = await totalCustomersTask;

model.MyTotalCustomers = await myTotalCustomersTask;
model.MyClosedCustomers = await myClosedCustomersTask;
model.MyNotStartedCustomers = await myNotStartedCustomers;

...

Async is not the same thing as "parallel", though async operations can be run in parallel.

answered Jul 26 '19 at 17:21

Chris Pratt

232,153
36
385
444

4

No, that [won't work with EF](https://stackoverflow.com/q/24702183/11683). You will have to `await` the previous task to be able to call for another one even without `await`. – GSerg Jul 26 '19 at 17:24
Yes it will. I've done it. It's perfectly fine. I know what you're referring to, but you've misunderstood the docs. See my comment below the question. – Chris Pratt Jul 26 '19 at 17:26
1

I'm confused then. The documentation quoted in that answer seems to claim that *EF will detect if the developer attempts to execute two async operations at one time and throw.* Is that not true? And if it is not, how does it handle transactional integrity? Does it promote to a distributed transaction? Or do the queries go through the same connection/transaction, still sequentially, but without an order guarantee? Is there a reference for that? – GSerg Jul 26 '19 at 17:33
Well, for one, that answer is from 2014, and is talking about EF, not EF Core. I'm honestly not sure what EF Core does, because I instinctively know not to try to do parallel atomic operations. However, again, we're talking about things that modify data and things that just read data. Modifications in parallel are a real problem. Queries in parallel is nothing. The database routinely handles queries in parallel all the time. Here, the OP is doing counts. Counts can absolutely be run in parallel, and EF will not complain. – Chris Pratt Jul 26 '19 at 17:37
@GSerg You are right and Chris is wrong - EF Core really uses something called `AsyncMonitor` for detecting attemps for multiple async calls and throws exception. This *might* work only if the tasks are completed before the next `CountAsync` call. – Ivan Stoev Jul 26 '19 at 17:38
@IvanStoev: Sorry. That's simply wrong. It 100% works. Try it yourself. Again, I only do this for things like selects and counts, though, because those do not cause issues in parallel. Maybe EF/EF Core will monitor and block certain operations, but it doesn't block these. – Chris Pratt Jul 26 '19 at 17:40
Okay, I see. I would not agree that queries in parallel are nothing though, as depending on the isolation mode and locking, order or selects may be important. E.g. it is a documented deadlock-avoiding technique to always run selects in the same order. – GSerg Jul 26 '19 at 17:41
@ChrisPratt I've tried it (with latest EF Core 2.2.6, SqlServer) and have to admit that it doesn't throw exception. But queries are executed serially one after other - you can see that with logging turned on. So you are partially right and partially wrong :) – Ivan Stoev Jul 26 '19 at 18:02
@ChrisPratt - I can confirm the way you are suggesting will work. It's returning the correct data and not giving an error. Would you recommend doing it the way you suggested or just leaving it the way I have it? I put a simple timer on it and although its almost negligible (30ms diff), the way I have it is faster. – Ben Jul 26 '19 at 18:04
3

@Ben See my previous comment. Although it won't throw exception, the queries won't run in parallel. The only way to get parallel queries is to use separate db context instances. Period. – Ivan Stoev Jul 26 '19 at 18:13
I will leave it the way I have it. For the last part of my question, is there a better way to write my method? "Better" meaning increased performance or more standard. – Ben Jul 26 '19 at 18:23
1

@Ben At the C# level, not necessarily. At the SQL level, it *might* be be faster to create a view that calculates all your counts in one pass, using `count(case when closed = 1 then 1 else null end) as closed_customers` etc. – GSerg Jul 26 '19 at 18:26
@IvanStoev: How did you try it? In something like IIS Express *everything* happens serially because there's only one thread. I'm using this in production code. Queries run in parallel. – Chris Pratt Jul 26 '19 at 18:56
@Ben, you'd have to perf it in a production-like environment. 30ms is within margin of error, so I'd imagine you're single-threaded, in which case it all happens serially no matter what you do. – Chris Pratt Jul 26 '19 at 18:59
@ChrisPratt They can run in parallel in the sense that the tasks are all in flight at once, but as far as the server is concerned, I don't see how anything else could happen other than the queries arriving strictly one by one. I don't see how it could work otherwise on the SqlConnection level. – GSerg Jul 26 '19 at 19:08
1

@Chris Not sure what you mean by production environment. I'm using simple Console App. But it doesn't matter, you are not spawning explicitly threads (`Task.Run`), so async tasks are "running", but not the SQL queries. It is EF Core DbContext async monitor which serializes them. Again, you can see that if you turn EF Core command execution logging on and look at command executing/executed logs. In the exact same environment, if I change the queries to use separate db context instances, the log shows parallel command execution. Shortly, it's EF Core db context instance level serialization. – Ivan Stoev Jul 26 '19 at 19:24
Can't get this to work with efcore 7 - if I remove the awaits, it throws this error "A second operation was started on this context instance before a previous operation completed. This is usually caused by different threads concurrently using the same instance of DbContext." – Pete Feb 27 '23 at 15:24

Async/Await Multiple CountAsync Database Calls EFCore

1 Answers1