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.

Solution 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.

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

Solution 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

Solution 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.

View more solutions

Comments

[Jone Polvora]

It will throw an error if the table has no records. To solve it, just replace .First() with .FirstOrDefault() and remember to check if the result is not null.

[Prageeth godage]

I also have same problem because I currently used DAPPER and it has query multiple option to retrieve multiple queries in single call.adrift solution is admirable witch I already think it was not possible in EF. many thanks adrift.

[Jamie Nordmeyer]

Beautifully done! I needed this for a paging grid of data, as I'm sure the other users did, and I would've never thought of this on my own, so thanks so much! You have my up-vote!

[CodeHacker]

BAM! Good one! Just perfect for a simple query like I'm using

[Michael Freidgeim]

Caching the total can cause inconsistency, if number of records changed between the first and subsequent page calls

[Michael Freidgeim]

It's a clever trick, but to have code maintainable it's better to have simple design with 2 calls

[Bryan]

it can, but often it doesn't matter, especially if there are many ages of results. When I needed the count and results together a single query was too slow and hard to read compared with two queries.

[xr280xr]

Just make sure your cached total count is cached specifically for any where clause. If your first query is ctx.People.Where (p => p.Name.StartsWith("A")), you don't want to reuse the total count on the next query ctx.People.Where (p => p.Name.StartsWith("B"))

[Matt Vukomanovic]

Note this also doesn't work correctly if you skip past the entire row set It would need to be changed to .FirstOrDefault() or .ToList() instead of .First()

[Rudey]

This probably never performs better than just doing two queries. My answer improves a little bit on this, but unfortunately the decreased performance still isn't worth it.

[Jonathan ANTOINE]

won't i query the count for each item ?

[Rudey]

No. The database engine will optimize the query and only perform the count once.

[SimonGates]

@JonathaANTOINE if you're using EFCore >= 1.1.x then yes it will.

[Brian McCord]

Just tested this on EFCore 2.21. Only produces one query when written as above. However, if instead of specifying each field, you do a Person = p, it will produce a count for each row.

[MÇT]

It seems that it has been solved at EF Core 3.0.0 according to this: github.com/aspnet/EntityFrameworkCore/issues/…

[moreginger]

I liked the look of this, but even with FirstOrDefault it doesn't give you the total if you have records but skip them all (i.e. with a page number that is too high).

[Matt]

what if we're not in Core at all? will this work in EF 6.x?

[Владимiръ]

This is evil. You should never run parallel operations on the same DB context instance. docs.microsoft.com/en-us/ef/core/querying/async

[Deepak Shaw]

the 'totalCount" has a limitation, will give ZERO after row finished, which is incorrect. I have improvised the last a bit code var totalCount = results.FirstOrDefault()?.TotalCount ?? await query.CountAsync();

[Rudey]

@DeepakShaw good point, I've edited my answer to include your fix.

[sommmen]

Has anyone tried this in .net 6 (ef core 6) because it looks like this will produce 2 queries now.