在实体框架 4.1 中查询一页数据并获取总计数的更好方法？

Question

目前，当我需要运行一个将与分页一起使用的查询时，我会这样做：

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

这有效，但我想知道是否可以在仍然使用 linq 的同时改进它以提高效率？我想不出一种方法可以将计数与数据检索结合到一次数据库访问中，而无需使用存储过程。

Answer 1

以下查询将在一次数据库访问中获取计数和页结果，但如果您检查 LINQPad 中的 SQL，您会发现它不是很漂亮。我只能想象更复杂的查询会是什么样子。

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

对于像这样的简单查询，您可能可以使用任一方法（2 次访问数据库，或使用 GroupBy 在 1 次行程中完成），并且不会注意到太大的差异。对于任何复杂的事情，我认为存储过程将是最好的解决方案。

Answer 2

Jeff Ogata 的答案可以稍微优化一下。

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

这几乎做了同样的事情，只是它不会用不必要的 GROUP BY 来打扰数据库。当您不确定查询将至少包含一个结果，并且不希望它抛出异常时，您可以通过以下方式（尽管不太干净）获取totalCount

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

：变体：

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

Answer 3

对于使用 EF Core >= 1.1.x && 的用户的重要说明< 3.0.0：

当时我正在寻找此问题的解决方案，并且此页面在 Google 术语“EF Core Paging Total Count”中排名第一。

检查 SQL 探查器后我发现 EF 为返回的每一行生成一个SELECT COUNT(*)。我已经厌倦了此页面上提供的每个解决方案。

这是使用 EF Core 2.1.4 和 EF Core 2.1.4 进行测试的。 SQL Server 2014。最后我不得不将它们作为两个单独的查询来执行，如下所示。至少对我来说，这并不是世界末日。

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             
};

EF Core 团队似乎已经意识到这一点：

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

Answer 4

我建议对第一页进行两次查询，一次查询总数，一次查询第一页或结果。

缓存总计数，以便在您超出第一页时使用。