Linq-to-entities, get results + row count in one query

Question

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. The basic idea is to populate a gridview and create custom paging. So, I need the results and row count as well. In SQL this would be something like: ``` SELECT COUNT(id), Id, Name... FROM ... WHERE ... ``` Getting everything in a nice simple query. However, I'd like to be consistent and use Linq2Entities. 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. I've tried this: ``` 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 recommend…

Accepted Answer

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-to-entities, get results + row count in one query

Solution

Problem

Related problems