Linq到实体,在一个查询中获取结果+行计数 [英] Linq-to-entities, get results + row count in one query
问题描述
我已经看到了这件事情多个问题,但是他们是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();
这篇关于Linq到实体,在一个查询中获取结果+行计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!