How does entity framework count total rows in one query?

entity-framework-6 pagination sql

Question

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?

1
1
7/2/2015 4:38:06 PM

Popular Answer

Your PagedList method is actually making two database calls.

  1. int total = source.Count();
  2. source.Skip(pageIndex * pageSize).Take(pageSize).ToList()

The query you posted would be from 2

1
7/2/2015 4:41:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow