Is it good practice, in Asp.Net MVC or Asp.Net Web API, to have every controller actions that query the database (even the simplest query) to use async/await pattern?
I know using async/await adds complexity, but does adding it worth it? Even for the simplest query?
Entity Framework uses database and need access to the database server. With EF you'll need to connect the databse server and wait for the server to respond to your request.
If your application uses disk or network (like access to a database) read/write then it is doing I/O operation. It's a good practice that every I/O operation should use async/await pattern that is what EF6 exposes many async operations that you can use.
I/O bound refers to a condition in which the time it takes to complete a computation is determined principally by the period spent waiting for input/output operations to be completed. Source : Wikipedia
Each ASP.Net Web API request use a thread that are given by .Net Framework thread pool. If you use synchronous method for ASP.Net Web API actions so the I/O bound operation (database access) will block the thread and wait for the database to respond. The thread used by your request will be blocked and not returned to the thread pool.
The maximum thread that are used by the tread pool is 5000 (.Net 4.5). If your application is a large application that maximum can be reached rapidly. If no thread is available in the thread pool so new requests will be added to the queue. If your server queue becomes full, it will reject requests with an HTTP 503 status whic stands for "Server Too Busy".
If your ASP.Net Web API actions are using async/await pattern then each I/O bound operation will free the current request's thread. This thread can be used by another request. If the I/O bound operation finished its task then another thread is given to process the rest of your ASP.Net Web API action method.
So to answer to your quesiton. Every action of your ASP.Net Web API that need access to your database should use async/await pattern if your application can have a lot of concurrency. Even if your applicaiton is not a larger application it is always recommended to use async/await for I/O bound operations.
You can check this article. It talk about "Using Asynchronous Methods" for ASP.Net MVC but the majority of the recommendations can be used for ASP.Net Web API.