How to Count Rows Within Entityframework Without Loading Contents

How to COUNT rows within EntityFramework without loading contents?

Query syntax:

var count = (from o in context.MyContainer
             where o.ID == '1'
             from t in o.MyTable
             select t).Count();

Method syntax:

var count = context.MyContainer
            .Where(o => o.ID == '1')
            .SelectMany(o => o.MyTable)
            .Count()

Both generate the same SQL query.

Get rowcount of large table using Entity framework

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()

Get total row count in Entity Framework

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.

Counting in Entity Framework

sorry for bothering ya, just found the solution!

with .Distinct().

   ObjGetIndexedViewResult.TotRecord = listDocuments.Distinct().AsNoTracking().Select(x => new { x.id }).Count();

Entity Framework 5 - Loads all rows on count

1. Why?

   Because it is not made for this purpose.

2. And how can I change this behavior?

   You really can't - only if you start to mess around with the Expression Provider.

To Note:

If we retrieve 50 records and on each row we do 'count', we will have more than 50(!) requests to MSSQL , as a result - very bad performance.

The best solution is to do something like this:

ct.Country.Select(v => new { Country = v, TotalStreets = v.Streets.Count() });

Please not that there is only one request.

When I call Count method in Entity Framework, does it process all the columns or just one or what?

I did some tests a while ago and found out that EF does a count on the server, it sends a query with a SELECT COUNT so it does not load all records for sure.

about the columns, if you are referring to the difference between COUNT(*) or COUNT(Id) or COUNT(1) I have read somewhere a while ago that for SQL Server there is no difference, the COUNT(*) is optimized as COUNT(1) anyway.

you could read many articles online or question here on SO... not excatly 100% what you asked but similar topics on performances of EF and ORM...

How to COUNT rows within EntityFramework without loading contents?

http://ayende.com/blog/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis

How to optimize Entity Framework Queries

How to find the number of rows in a table in greenplum without using count(*)

You can try below query for approximate row count.

select reltuples from pg_class WHERE relname = 'table1';

---

Related Topics