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 recommended approach here?

UPDATE:

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

And next row:

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

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

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

Answer 1

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