Get total row count in Entity Framework

Question

I'm using Entity Framework to get the total row count for a table. I simply want the row count, no where clause or anything like that. The following query works, but is slow. It took about 7 seconds to return the count of 4475.

My guess here is that it's iterating through the entire table, just like how IEnumerable.Count() extension method works.

Is there a way I can get the total row count "quickly"? is there a better way?

    public int GetLogCount()
    {
        using (var context = new my_db_entities(connection_string))
        {
            return context.Logs.Count();
        }
    }

How to COUNT rows within EntityFramework without loading contents? http://stackoverflow.com/questions/890381/how-to-count-rows-within-entityframework-without-loading-contents — sevdalone, Aug 28 '13 at 14:31
Make sure that context.Logs is a DbSet, IDbSet, or an IQueryable, not an IEnumerable. If it is an IEnumerable, the entire table will be fetched and counted. If it is an IQueryable, the query will be generated to count the rows in the database that will be something like "SELECT COUNT(*) FROM dbo.Logs" (If it is an IdbSet or DbSet, it will be treated as an IQueryable) — Grax32, Aug 28 '13 at 15:17

score 8 · Answer 1 · answered Aug 28 '13 at 15:10

8

You can even fire Raw SQL query using entity framework as below:

var sql = "SELECT COUNT(*) FROM dbo.Logs";
var total = context.Database.SqlQuery<int>(sql).Single();

answered Aug 28 '13 at 15:10

Bhushan Firake

9,338
5
44
79

score 6 · Accepted Answer · answered Aug 28 '13 at 14:28

That is the way to get your row count using Entity Framework. You will probably see faster performance on the second+ queries as there is an initialization cost the first time that you run it. (And it should be generating a Select Count() query here, not iterating through each row).

If you are interested in a faster way to get the raw row count in a table, then you might want to try using a mini ORM like Dapper or OrmLite.

You should also make sure that your table is properly defined (at the very least, that it has a Primary Key), as failure to do this can also affect the time to count rows in the table.

Khan · Answer 3 · 2013-08-28T16:04:03.607

If you have access to do so, it would be much quicker to query the sys tables to pull this information.

E.g.

public Int64 GetLogCount()
{
    var tableNameParam = new SqlParameter("TableName", "Logs");
    var schemaNameParam = new SqlParameter("SchemaName", "dbo");
    using (var context = new my_db_entities(connection_string))
    {
        var query = @"
            SELECT ISNULL([RowCount],0)
            FROM (
                SELECT  SchemaName,
                        TableName, 
                        Sum(I.rowcnt) [RowCount]
                FROM    sysindexes I 
                        JOIN sysobjects O (nolock) ON I.id = o.id AND o.type = 'U' 
                        JOIN (
                            SELECT  so.object_id, 
                                    ss.name as SchemaName,
                                    so.name as TableName
                                FROM   sys.objects SO (nolock) 
                                    JOIN sys.schemas SS (nolock) ON ss.schema_id = so.schema_id
                        ) SN 
                            ON SN.object_id = o.id 
                WHERE   I.indid IN ( 0, 1 )
                AND     TableName = @TableName AND SchemaName = @SchemaName
                GROUP BY 
                        SchemaName, TableName
            ) A
        ";

        return context.ExecuteStoreQuery<Int64>(query, tableNameParam, schemaNameParam).First();
    }
}

MSDN says the sys.partitions.rows "Indicates the approximate number of rows in this partition." http://technet.microsoft.com/en-us/library/ms175012.aspx — cadrell0, Aug 28 '13 at 15:17
http://technet.microsoft.com/en-us/library/ms190283.aspx "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature." Also, from the documentation, it sounds like this may not work if the table is partitioned. — cadrell0, Aug 28 '13 at 16:55

Get total row count in Entity Framework

3 Answers3