Entity Framework Core 计数没有最佳性能 [英] 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
...运行速度非常快.
.. which runs very fast.
如何生成将搜索条件传递给计数的第二个查询?
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:
改进翻译，使更多查询能够成功执行，并在数据库中(而不是在内存中)评估更多逻辑.
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.
但请注意，随着改进，新版本引入了错误/回归(正如您在此处看到的 例如，EFCore 为简单的 LEFT OUTER join 返回太多列)，因此请自行承担风险(并再次考虑第一个选项，即 哪个适合您 :)
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 :)
这篇关于Entity Framework Core 计数没有最佳性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
