I'm in the process of swapping out some Entity Framework queries for hand-crafted SQL using Dapper. All is going well so far - the only bit I'm struggling with is implementing efficient pagination inside a single DB query.
Our current code is like this:
public PagedList(IQueryable<T> source, int pageIndex, int pageSize)
{
int total = source.Count();
TotalCount = total;
TotalPages = total / pageSize;
if (total % pageSize > 0)
TotalPages++;
PageSize = pageSize;
PageIndex = pageIndex;
AddRange(source.Skip(pageIndex * pageSize).Take(pageSize).ToList());
}
if I debug over this, I can see the total number of rows coming back from Source.Count().
However when I use Glimpse to check the generated SQL, I can only see one query going off to the database. Something like:
SELECT TOP 30 field1, field2
FROM (
SELECT field1, field2, row_number()
OVER (ORDER BY [Project1].[Score] DESC) AS [row_number] WHERE ..) AS Project1
WHERE project1.row_number > 30
I can't see any COUNT()
expressions inside here, nor are there two queries being issued. I'm really confused - how has it counted the number of rows inside the sub-query?
Your PagedList
method is actually making two database calls.
int total = source.Count();
source.Skip(pageIndex * pageSize).Take(pageSize).ToList()
The query you posted would be from 2