实体框架核心数没有最佳性能 [英] Entity Framework Core count does not have optimal performance
问题描述
我需要使用某个过滤器来获取记录数量.
I need to get the amount of records with a certain filter.
理论上，此指令:
_dbContext.People.Count (w => w.Type == 1);
它应该生成如下的SQL:
It should generate SQL like:
Select count (*)
from People
Where Type = 1
但是，生成的SQL是:
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.
如果我只是这样做:
_dbContext.People.Count ();
实体框架生成以下查询:
Entity Framework generates the following query:
Select count (*)
from People
..它运行非常快.
如何生成第二个将搜索条件传递给计数的查询?
How to generate this second query passing search criteria to the count?
推荐答案
此处没有太多要回答的内容.如果您的ORM工具无法通过简单的LINQ查询生成预期的SQL查询，则无法通过重写查询来使其实现(并且一开始就不应该这样做).
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具有在LINQ查询中进行客户端/数据库混合评估的概念，使他们可以发布EF Core版本的查询，例如您的案例，它的处理不完全/效率非常低.
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.
摘录自不在EF Core中的功能(请注意单词 not )和 Roadmap :
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").
那有什么选择呢?
- 首先，远离EF Core，直到它真正有用为止.回到EF6，它没有这样的问题.
- 如果您不能使用EF6，请保持最新的EF Core版本.
例如，在v1.0.1和v1.1.0中，您都查询生成了预期的SQL(已测试)，因此您只需进行升级即可解决具体问题.
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 :)
这篇关于实体框架核心数没有最佳性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
