.NET Core EF, when to avoid asynchronous database calls

Question

I'm currently in the process of writing a simple web application, which utilize a mssql database to store data. As it is right now, I try to setup my business logic layer and thus structure it in the best possible way.

Currently, all of my calls are made with Linq and actually none of them are asynchronous. I'm fairly new to asynchronous programming, and are generally curious to know, for which queries I should use asynchronous calls.

As far as I understand it makes sense to use asynchronous database calls, because it frees up memory on my main thread and doesn't block the UI. Also, asynchronous calls can be run concurrently, which means that more transactions can be done at the same time.

However, consider that I add multiple entities asynchronously within my context, and use the SaveChangesAsync() method to conclude my context. Isn't it then redundant to have multiple AddAsync() methods in my context, because SaveChangesAsync() will run everything asynchronously anyway?

Conclusively, from what I have read and seen, a lot of people prefer to use asynchronous database calls. That is, rather the rule than the exception.

If so, in what cases would I avoid asynchronous database calls, what general rules should i adhere to when adding asynchronous calls in my business logic layer?

Answer 1

Async in MVC is about freeing up the request handling threads so that they can respond to additional requests. It doesn't make code faster, it actually makes it slightly slower. When it comes to threads, it's fairly easy to see what it is doing in that it hands off to a worker thread. What happens after that async method completes depends on the synchronization context. In most cases it will resume on the worker thread.

So using [thread#] prefixes:

[1] using(var context = new AppContext())
    {
[1]     var orders = await context.Orders
            .Where(x => x.CustomerId == customerId)
            .OrderBy(x => x.OrderDate)
            .ToListAsync();
[2]     foreach(var order in orders)
        {
            //...
        }
    }

await/async means that C# will hand off to a worker thread to execute the code, in this case to EF to query the Orders. It creates a resumption point for the remaining code that will be executed once that operation concludes. Meanwhile, Thread #1 concludes and MVC can use it to respond to other requests.

The consideration when considering asynchronous calls should be weighing the time needed to perform the operation, and the time/resources needed to create resumption paths and resume code. Asynchronous calls are most useful when facing relatively infrequent large, expensive operations that could tie up response threads which could otherwise be servicing requests while the originator waits. For quick, common operations, async can slow things down. So as a general rule, if an operation is going to take more than a second or two to run it is well suited to async/await. For quick queries, I leave them synchronous by default.