Is it right to assume that FirstOrDefaultAsync runs on the client (web server) side and Where runs on host (SQL server) side, thus using Where is much better than using FirstOrDefaultAsync?

If the answer is yes, then how can I leverage the power of async since I can't call FirstOrDefaultAsync after Where?

How can await in this case to use & optimize my recourses to the best extent?

CodePudding user response：

Two different things here

• Where defines filter which will be used later for generating SQL and do not execute query.
• FirstOrDefaultAsync defines filter and executes query on the SQL Server

So all of your assumptions are incorrect.

CodePudding user response：

"I can't call FirstOrDefaultAsync after Where?"

var result = await context.Entities
    .OrderBy(x => x.StartDate)
    .FirstOrDefaultAsync(x => /* some condition */);  

equals...

var result = await context.Entities
    .Where(x => /* some condition */)
    .OrderBy(x => x.StartDate)
    .FirstOrDefaultAsync();  

Why use Where over the expression right in the FirstOrDefault? Reasons include:

• Readability / Organization.
• Conditional query composition.

An example of conditional composition: If you have a startDate parameter that is optional that you want to check and filter on if present:

Embedded parameter check:

var result = await context.Entities
    .OrderBy(x => x.StartDate)
    .FirstOrDefaultAsync(x => !startDate.HasValue || x.StartDate >= startDate.Value);  

Conditional composition:

var query = context.Entities.AsQueryable();
if(startDate.HasValue)
    query = query.Where(x => x.StartDate >= startDate.Value);

var results = await query
    .OrderBy(x => x.StartDate)
    .FirstOrDefaultAsync();  

When you start adding several optional conditions, the embedded conditional checks can get rather messy to read and error prone with the risk of mismatching parenthesis "()" between all of the && and || conditions. It ultimately means more work for EF and the resulting SQL to sift through. Conditional composition only adds the filtering expression if it is needed, resulting in simpler, faster queries when the conditions aren't used.

Key points to consider:

• Only use First* methods with an OrderBy* clause to ensure they are repeatable.
• Only use *OrDefault methods where you explicitly expect no result or a default value. Otherwise use First/Single etc. and handle the exception.
• Don't fall into the trap believing that async makes queries faster and should be used by default. It actually makes queries slightly slower. (Due to context switching)

async is about making things like web requests more responsive during particularly expensive operations. If something is going to take, say, more than 500ms to run, async can help ensure the web server request threads are responding to new requests while that is running. If every request is delegating to a worker thread with awaited resumption points, what should be "in & out" quick hits end up taking longer on average, and messier to debug. It can also be used in situations where a particular operation will be called quite frequently by a lot of potential requests, though in that case it is firstly important to ensure it's running on hardware scaled for that amount of traffic.