实体框架如何计算一个查询中的总行数？

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.

我正在使用Dapper交换手工SQL的一些实体框架查询。到目前为止一切顺利 - 我唯一要努力的是在单个数据库查询中实现高效的分页。

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

如果我对此进行调试,我可以看到从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:

但是,当我使用Glimpse检查生成的SQL时,我只能看到一个查询到数据库。就像是:

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?

我在这里看不到任何COUNT()表达式,也没有发出两个查询。我真的很困惑 - 它如何计算子查询中的行数?

1 个解决方案

#1

Your PagedList method is actually making two database calls.

您的PagedList方法实际上是进行两次数据库调用。

int total = source.Count();

int total = source.Count();

source.Skip(pageIndex * pageSize).Take(pageSize).ToList()

source.Skip(pageIndex * pageSize).Take(pageSize).ToList()

The query you posted would be from 2

您发布的查询将来自2

#1