Entity Framework Core count does not have optimal performance

Question

I need to get the amount of records with a certain filter.

Theoretically this instruction:

_dbContext.People.Count (w => w.Type == 1); 

It should generate SQL like:

Select count (*) from People Where Type = 1 

However, the generated SQL is:

Select Id, Name, Type, DateCreated, DateLastUpdate, Address from People Where Type = 1 

The query being generated takes much longer to run in a database with many records.

I need to generate the first query.

If I just do this:

_dbContext.People.Count (); 

Entity Framework generates the following query:

Select count (*) from People 

.. which runs very fast.

How to generate this second query passing search criteria to the count?

Answer 1

There is not much to answer here. If your ORM tool does not produce the expected SQL query from a simple LINQ query, there is no way you can let it do that by rewriting the query (and you shouldn't be doing that at the first place).

EF Core has a concept of mixed client/database evaluation in LINQ queries which allows them to release EF Core versions with incomplete/very inefficient query processing like in your case.

Excerpt from Features not in EF Core (note the word not) and Roadmap:

Improved translation to enable more queries to successfully execute, with more logic being evaluated in the database (rather than in-memory).

Shortly, they are planning to improve the query processing, but we don't know when will that happen and what level of degree (remember the mixed mode allows them to consider query "working").

So what are the options?

• First, stay away from EF Core until it becomes really useful. Go back to EF6, it's has no such issues.
• If you can't use EF6, then stay updated with the latest EF Core version.

For instance, in both v1.0.1 and v1.1.0 you query generates the intended SQL (tested), so you can simply upgrade and the concrete issue will be gone.

But note that along with improvements the new releases introduce bugs/regressions (as you can see here EFCore returning too many columns for a simple LEFT OUTER join for instance), so do that on your own risk (and consider the first option again, i.e. Which One Is Right for You :)

Answer 2

Try to use this lambda expression for execute query faster.

_dbContext.People.select(x=> x.id).Count();