fungus1487 fungus1487 - 7 months ago 60
SQL Question

Entity Framework 5.0 Poor COUNT Performance

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?

Answer

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(); 
}
Comments