I am using entity framework to get a simple rowcount of table with millions of records and I don't want any where clause. I tried to use the Count method but it is taking very long to fetch the count. Is there any efficient way to fetch the count with waiting that long?
2 Answers
I think you are first retrieving all records and then count them. You should directly count your records.
using(DatabaseContext db = new DatabaseContext())
{
//what I think you are doing:
int countLong = db.UserAccount.ToList().Count();
//what you should be doing
int countShort = db.UserAccount.Count();
//include where clauses inside count, wrong way:
int countLong2 = db.UserAccount.ToList().Count(x => x.Active);
//include where clauses inside count, good way:
int countShort2 = db.UserAccount.Count(x => x.Active);
//or if you don't like lambda expressions.
int countLong3 = (from x in db.UserAccount
//where x.Active //optional
select x).ToList().Count();
int countShort3 = (from x in db.UserAccount
//where x.Active //optional
select x).Count();
}
DatabaseContext would be your class that extends DbContext class
Comments
You can try as shown below.This query will return a IQueryable result.That means count operation happens in the database.
Query Based :
var countVal = (from a in context.yourTable
select a).Count();
Method Based :
var countVal = context.yourTable.Count()
2 Comments
jeroenh
or simply
context.yourTable.Count()
Efrain
I found that this translates to some very poorly performing SQL, that iterates through every row in the DB:
SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Log] AS [Extent1] ) AS [GroupBy1] - I recommend using raw SQL, I haven't found another way.
IEnumerableversion ofCount(), your application would be retrieving all rows and then counting them. If you're using theIQueryableversion ofCount(), the database server would be doing the count and should be fast.