更好的方式来查询数据页面并获得实体框架中的总数4.1？ [英] Better way to query a page of data and get total count in entity framework 4.1?

问题描述

目前，当我需要运行一个将被使用w / paging的查询时，我会这样做：

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？我想不出一种方法将一个单一行程中的数据检索与使用存储过程的DB w / o组合。

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.

推荐答案

下面的查询将获得计数和页面结果一次到数据库，但如果你检查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？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！