实体框架 COUNT 性能不佳 [英] Entity Framework Poor COUNT Performance

问题描述

使用 Entity Framework 5.0 和 MySql 连接器 6.6.6.0 进行基于计数的查询时，我们遇到了非常糟糕的性能.我们的数据结构如下:

<上一页>表:帖子===========ID INT 主键MemberID INT NOT NULLSiteID INT NOT NULL说明 VARCHAR(255) NOT NULL图像 VARCHAR(255) 非空CreatedDate 日期时间 NULL

并使用带有 linq 查询的实体框架，如下所示:

<上一页>var count = entity.Post.Where(p =>p.SiteID == 1 && p.CreatedDate != null).Count();

我们得到以下生成的 SQL:

<上一页>选择`Extent1`.`ID`,`Extent1`.`MemberID`,`Extent1`.`SiteID`,`Extent1`.`描述`,`Extent1`.`图像`,`Extent1`.`CreatedDate`FROM `Post` AS `Extent1`WHERE (`Extent1`.`SiteID` = 1) AND (`Extent1`.`CreatedDate` 不为空)

这会读取所有记录并在内存中对它们进行计数...非常低效，因为它应该看起来像这样:

<上一页>从 `Post` 中选择 COUNT(ID)，其中 `SiteID` = 1 并且 `CreatedDate` 不为 NULL；

是否有提示实体我们不想将所有记录读入内存而只执行 SQL COUNT?

解决方案

试试

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

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

在页面底部有这个:

使用 Query 统计相关实体而不加载它们

有时，知道有多少实体与数据库中的另一个实体相关，而不会实际产生加载所有这些实体的成本，这很有用.可以使用带有 LINQ Count 方法的 Query 方法来执行此操作.例如:

使用 (var context = new BloggingContext()){var blog = context.Blogs.Find(1);//计算博客有多少帖子var postCount = context.Entry(博客).Collection(b => b.Posts).询问().数数();}

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

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

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

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;

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

解决方案

Try

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

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

Has this at the bottom of the page:

Using Query to count related entities without loading them

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屋！