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?

Solution 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 :)

Solution 2

Try to use this lambda expression for execute query faster.

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

Solution 3

Try this

(from x in _dbContext.People where x.Type == 1 select x).Count();

or you could do the async version of it like:

await (from x in _dbContext.People where x.Type == 1 select x).CountAsync();

and if those don't work out for you, then you could at least make the query more efficient by doing:

(from x in _dbContext.People where x.Type == 1 select x.Id).Count();

or

await (from x in _dbContext.People where x.Type == 1 select x.Id).CountAsync();

Solution 4

If you want to optimize performance and the current EF provider is not not (yet) capable of producing the desired query, you can always rely on raw SQL.

Obviously, this is a trade-off as you are using EF to avoid writing SQL directly, but using raw SQL can be useful if the query you want to perform can't be expressed using LINQ, or if using a LINQ query is resulting in inefficient SQL being sent to the database.

A sample raw SQL query would look like this:

var results = _context.People.FromSql("SELECT Id, Name, Type, " +
                                      "FROM People " +
                                      "WHERE Type = @p0",                                                     
                                      1);

As far as I know, raw SQL queries passed to the FromSql extension method currently require that you return a model type, i.e. returning a scalar result may not yet be supported.

You can however always go back to plain ADO.NET queries:

using (var connection = _context.Database.GetDbConnection())
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM People WHERE Type = 1";
        var result = command.ExecuteScalar().ToString();
    }
}

Solution 5

It seems that there has been some problem with one of the early releases of Entity Framework Core. Unfortunately you have not specified exact version so I am not able to dig into EF source code to tell what exactly has gone wrong.

To test this scenario, I have installed the latest EF Core package and managed to get correct result.

Here is my test program:

And here is SQL what gets generated captured by SQL Server Profiler:

As you can see it matches all the expectations.

Here is the excerpt from packages.config file:

...
<package id="Microsoft.EntityFrameworkCore" version="1.1.0" targetFramework="net452" />
...

So, in your situation the only solution is to update to the latest package which is 1.1.0 at the time of writing this.

View more solutions

Comments

[Theo]

Your 1st attempt "should" get you what you want. It appears that it is executing the IEnumerable version of .Count([predicate]) rather than the IQueriable version which would generate the expected SQL ... something is causing your code to choose the incorrect extension.

[binarydreams]

What type is _dbContext.People declared as?

[Renatto Machado]

@Theo I'm do not using the IEnumerable versinho. I alredy verify that!

[Renatto Machado]

@RichardSzalay It's like DbSet<People>

[Theo]

How did you verify that? I guarantee that's what is happening, or you would be getting the SQL you expect.

[Dmitry]

What version of EF Core you are using?

[Renatto Machado]

@Dmitry The version of SDK is 1.0.0-preview2-003121

[lerthe61]

I saw this behavior before with SQL CE edition. I believe that this happens due issue in IQueriable to SQL code

[Renatto Machado]

But the objects People and method where are IQueriable. I have try with ".AsQueriable()" too, but not working.

[Dmitry]

Try to upgrade to EF Core 1.1.0. EF team made some improvements about query generation - I found more optimal JOIN-queries on my projects, my be your trouble will solve too. EF Core is young.

[Renatto Machado]

Upgrade without know if that resolve my problem don't is a good solution.

[Gabriel Rainha]

@Renatto Machado I just created an empty project and a sample database with a table like yours, installed EF Core 1.1 and the query returned was the correct one. Perhaps you should really just update the library.

[Renatto Machado]

That not work for me. I need pass predicate on "Where" method and when I do that, the problem ocurrence.

[Raju Mali]

_dbContext.People.where(x=x.filterColumname == input parameter).select(x=> x.id).Count();

[Gabriel Rainha]

But the question is not asking how to Count on Linq, is about the query generated by EF Core when you Count.

[Ali Mohsan]

Thanks @Gabriel for explaining. To other people who have voted down this Please do mention the reason so we can improve :) Thanks anyways

[aruno]

understanding what your ORM can and can't rewrite here is the key - don't ever make any assumptions - especially in the early days of EFCore

[Dan Nguyen]

You don't want to load all of People into memory just to count how many people are there in the table. what happen if we have more than 1 million records? Your sever will be dead

[aruno]

Mostly fixed now in EFCore 2.1 - but watch out for log/console messages because some complex cases may not work.

[Maksymilian Mulawa]

If context is not created from scratch, the Close method on connection should be called not Dispose through "using". As any reuse of this DBConnection will not be possible unless Context gets recreated. So it's kind of sad, but before calling Open() you should check if it's not already open and also Close() it everytime you use it. :( github.com/aspnet/EntityFrameworkCore/issues/7810

[Giox]

what have you used to see the generated SQL query?

[Kaspars Ozols]

In this case I used SQL Server Profiler to monitor database server directly. It allows to see all the executed statements, even the ones not coming from EF. docs.microsoft.com/en-us/sql/tools/sql-server-profiler/… Unfortunately, it is deprecated now and you should use something called Extended Events instead.