Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
741 views
in Technique[技术] by (71.8m points)

c# - Entity Framework Core count does not have optimal performance

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...