实体框架不佳COUNT表现 [英] Entity Framework Poor COUNT Performance

问题描述

使用具有MySql Connector 6.6.6.0的Entity Framework 5.0进行基于计数的查询，我们的性能非常差。我们的数据结构如下所示：

We are experiencing very poor performance using Entity Framework 5.0 with MySql Connector 6.6.6.0 for count based queries. Our data structure looks like:

Table: Post
===========
ID           INT PRIMARY KEY
MemberID     INT NOT NULL
SiteID       INT NOT NULL
Description  VARCHAR(255) NOT NULL
Image        VARCHAR(255) NOT NULL
CreatedDate  DATETIME NULL

并使用具有如下的linq查询的实体框架：

And using entity framework with a linq query like the following:

var count = entities.Post.Where(p => 
    p.SiteID == 1 && p.CreatedDate != null).Count();

我们得到以下生成的SQL：

We get the following generated SQL:

SELECT
`Extent1`.`ID`, 
`Extent1`.`MemberID`, 
`Extent1`.`SiteID`, 
`Extent1`.`Description`, 
`Extent1`.`Image`, 
`Extent1`.`CreatedDate`
FROM `Post` AS `Extent1`
 WHERE (`Extent1`.`SiteID` = 1) AND (`Extent1`.`CreatedDate` IS NOT NULL)

这将读取所有记录并在内存中计数它们...效率很低因为它应该看起来像：

This reads all records and counts them in memory... Hugely inefficient as it should look something like:

SELECT COUNT(ID) FROM `Post` WHERE `SiteID` = 1 AND `CreatedDate` IS NOT NULL;

有没有提示实体，我们不想将所有记录读入内存，只执行SQL COUNT？

Is there anyway to hint to entity that we dont want to read all records into memory and just perform an SQL COUNT?

推荐答案

尝试

var count = entities.Post.Where(p => 
       p.SiteID == 1 && p.CreatedDate != null).Query().Count();

http://msdn.microsoft.com/en-us/data/jj574232.aspx

有这个在页面底部：

使用查询来计算相关实体而不加载它们

Using Query to count related entities without loading them

有时它是有用的知道有多少实体与数据库中的另一个实体相关，而不会实际承担所有这些实体的加载成本。可以使用具有LINQ Count方法的Query方法来执行此操作。例如：

Sometimes it is useful to know how many entities are related to another entity in the database without actually incurring the cost of loading all those entities. The Query method with the LINQ Count method can be used to do this. For example:

using (var context = new BloggingContext()) 
{ 
    var blog = context.Blogs.Find(1); 

    // Count how many posts the blog has  
    var postCount = context.Entry(blog) 
                          .Collection(b => b.Posts) 
                          .Query() 
                          .Count(); 
}

这篇关于实体框架不佳COUNT表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！