Better way to query a page of data and get total count in entity framework 4.1?

Question

Currently when I need to run a query that will be used w/ paging I do it something like this:

//Setup query (Typically much more complex)
var q = ctx.People.Where(p=>p.Name.StartsWith("A"));

//Get total result count prior to sorting
int total = q.Count();       

//Apply sort to query
q = q.OrderBy(p => p.Name);  

q.Select(p => new PersonResult
{
   Name = p.Name
}.Skip(skipRows).Take(pageSize).ToArray();

This works, but I wondered if it is possible to improve this to be more efficient while still using linq? I couldn't think of a way to combine the count w/ the data retrieval in a single trip to the DB w/o using a stored proc.

Answer 1

The following query will get the count and page results in one trip to the database, but if you check the SQL in LINQPad, you'll see that it's not very pretty. I can only imagine what it would look like for a more complex query.

var query = ctx.People.Where (p => p.Name.StartsWith("A"));

var page = query.OrderBy (p => p.Name)
                .Select (p => new PersonResult { Name = p.Name } )          
                .Skip(skipRows).Take(pageSize)
                .GroupBy (p => new { Total = query.Count() })
                .First();

int total = page.Key.Total;
var people = page.Select(p => p);

For a simple query like this, you could probably use either method (2 trips to the database, or using GroupBy to do it in 1 trip) and not notice much difference. For anything complex, I think a stored procedure would be the best solution.

Answer 2

Jeff Ogata's answer can be optimized a little bit.

var results = query.OrderBy(p => p.Name)
                   .Select(p => new
                   {
                       Person = new PersonResult { Name = p.Name },
                       TotalCount = query.Count()
                   })          
                   .Skip(skipRows).Take(pageSize)
                   .ToArray(); // query is executed once, here

var totalCount = results.First().TotalCount;
var people = results.Select(r => r.Person).ToArray();

This does pretty much the same thing except it won't bother the database with an unnecessary GROUP BY. When you are not certain your query will contain at least one result, and don't want it to ever throw an exception, you can get totalCount in the following (albeit less cleaner) way:

var totalCount = results.FirstOrDefault()?.TotalCount ?? query.Count();

And the async variant:

var totalCount = await results.FirstOrDefaultAsync()?.TotalCount ?? await query.CountAsync();

Answer 3

Important Note for People using EF Core >= 1.1.x && < 3.0.0:

At the time I was looking for solution to this and this page is/was Rank 1 for the google term "EF Core Paging Total Count".

Having checked the SQL profiler I have found EF generates a SELECT COUNT(*) for every row that is returned. I have tired every solution provided on this page.

This was tested using EF Core 2.1.4 & SQL Server 2014. In the end I had to perform them as two separate queries like so. Which, for me at least, isn't the end of the world.

var query = _db.Foo.AsQueryable(); // Add Where Filters Here.


var resultsTask = query.OrderBy(p => p.ID).Skip(request.Offset).Take(request.Limit).ToArrayAsync();
var countTask = query.CountAsync();

await Task.WhenAll(resultsTask, countTask);

return new Result()
{
    TotalCount = await countTask,
    Data = await resultsTask,
    Limit = request.Limit,
    Offset = request.Offset             
};

It looks like the EF Core team are aware of this:

https://github.com/aspnet/EntityFrameworkCore/issues/13739 https://github.com/aspnet/EntityFrameworkCore/issues/11186

Answer 4

I suggest making two queries for the first page, one for the total count and one for the first page or results.

Cache the total count for use as you move beyond the first page.