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

问题描述

目前，当我需要运行将用于分页的查询时，我会这样做:

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

这有效，但我想知道是否有可能在仍然使用 linq 的同时改进它以提高效率?我想不出一种方法来使用存储过程将计数与数据检索结合到数据库的单次旅行中.

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.

推荐答案

下面的查询会在一次数据库中得到count和page结果，但是如果你在LINQPad中查看SQL，你会发现它不是很漂亮.我只能想象一个更复杂的查询会是什么样子.

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

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

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.

这篇关于在实体框架 4.1 中查询一页数据并获取总数的更好方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！