Linq到实体，在一个查询中获取结果+行计数

本文介绍了Linq到实体，在一个查询中获取结果+行计数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经看到了这件事情多个问题，但是他们是2年（或以上）的时候，所以我想知道如果有什么改变了这一点。

I've seen multiple questions about this matter, however they were 2 years (or more) old, so I'd like to know if anything changed about this.

基本的想法是来填充GridView和创建自定义分页。所以，我需要的结果和行数以及

The basic idea is to populate a gridview and create custom paging. So, I need the results and row count as well.

在SQL这将是这样的：

In SQL this would be something like:

SELECT COUNT(id), Id, Name... FROM ... WHERE ...

让一切在一个不错的简单查询。不过，我想是一致的，并使用Linq2Entities。

Getting everything in a nice simple query. However, I'd like to be consistent and use Linq2Entities.

到目前为止，我用了两个查询（针对SQL Server）的方法，因为它只是工作。我想，虽然优化它，并使用一个单一的查询，而不是

So far I'm using the approach with two queries (against sql server), because it just works. I would like to optimize it though and use a single query instead.

我试过这样：

var query = from o in _db.Products select o; var prods = from o in query select new { Count = query.Count(), Products = query };

这产生具有实际上是不必要的交叉连接和其他的东西，我不很肮脏和长查询真正需要或想要的。

This produces a very nasty and long query with really unnecessary cross joins and other stuff which I don't really need or want.

有没有办法让所有实体的分页结果+计数一个简单的查询？什么是这里推荐的方法

Is there a way to get the paged results + count of all entities in a one simple query? What is the recommended approach here?

更新：

刚试过FutureQueries和无论是我做错了什么，或者它实际上执行两个查询。这说明我的SQL事件探查器：

Just tried FutureQueries and either I'm doing something wrong, or it actually executes two queries. This shows my sql profiler:

-- Query #1 SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE 1 = [Extent1].[CategoryID] ) AS [GroupBy1];

和下一行：

-- Query #1 SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[Price] AS [Price], [Extent1].[CategoryID] AS [CategoryID] FROM [dbo].[Products] AS [Extent1] WHERE 1 = [Extent1].[CategoryID];

C＃代码：

The C# code:

internal static List<Product> GetProducts(out int _count) { DatabaseEntities _db = new DatabaseEntities(); var query = from o in _db.Products where o.CategoryID == 1 select o; var count = query.FutureCount(); _count = count.Value; return query.Future().ToList(); }

我错过了什么？根据我的探查它除了在查询中添加的行完全相同的（ - 查询＃1）。

Did I miss something? According to my profiler it does exactly the same except that added row in the query (-- Query #1).

推荐答案

有看看将来的查询为此在 EntityFramework.Extended 。在该链接的页面使用第二个例子 FutureCount（）做的正是你想要的。在这里改编：

Have a look at Future Queries to do this in EntityFramework.Extended. The second example on that linked page uses FutureCount() to do exactly what you want. Adapted here:

var q = db.Products.Where(p => ...); var qCount = q.FutureCount(); var qPage = q.Skip((pageNumber-1)*pageSize).Take(pageSize).Future(); int total = qCount.Value; // Both queries are sent to the DB here. var tasks = qPage.ToList();