Is there a difference between calling the 'where' function in the beginning of the query and in the end in terms of performance?

asp.net-core c# entity-framework-core

Question

Sorry if this is too obvious, I was wondering is there a difference between the two blocks of code below in terms of performance and amount of data retrieved? will specifying the id in the end of the query (like in FirstOrDefaultAsync(b => b.BuyModelId == modelId)) brings all the table data first and then evaluate it against the id?

and will calling the Where function in the beginning (like this Where(b => b.BuyModelId == modelId)) will evaluate the data against the id and then call the include functions ?

So to summarize my question: is there a specific order to which those statements execute?

because I was worried that there might be some extra overhead in my calls to the database.

public async Task<BuyModel> GetModelById(int modelId)
{
    return await _applicationDbContext.BuyModels
         .Where(b => b.BuyModelId == modelId)
         .Include(b => b.Buyer)
         .Include(b => b.ItemsToBuy).ThenInclude(p => p.Category)
         .Include(b => b.ItemsToBuy).ThenInclude(p => p.SalesUser).FirstOrDefaultAsync();
}
public async Task<BuyModel> GetModelById(int modelId)
{
    return await _applicationDbContext.BuyModels
         .Include(b => b.Buyer)
         .Include(b => b.ItemsToBuy).ThenInclude(p => p.Category)
         .Include(b => b.ItemsToBuy).ThenInclude(p => p.SalesUser).FirstOrDefaultAsync(b => b.BuyModelId == modelId);
}
1
3
9/23/2019 12:28:38 PM

Accepted Answer

There is zero difference. Until the queryable is evaluated (using something like ToListAsync), everything just goes to constructing a SQL query that will eventually be sent.

That said, the latter version is preferred, since it's more clear what you're querying.

1
9/23/2019 12:43:32 PM

Popular Answer

As said in the comments, there are two layers to performance of LINQ-to-entities query.

First is what SQL is generated. In your scenario, I would guess that they will be the same. Only way to be sure is to have EF output the generated SQL.

If the generated SQL are different, then SQL can still create same execution plan. SQL server has lots of cleverness and magic when it comes to optimizing queries. There is big chance that both queries would result in same execution plan and thus same performance.

But if you really care about performance, you should really analyze both the generated SQL query and how SQL runs this query. Preferably on full and index-enabled one, so that execution plan is realistic.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow