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).

I would say that (in EF 4.1 and smaller at least), the 2 queries-way is still the best. — Raphaël Althaus, May 23 '12 at 16:00
@RaphaëlAlthaus, can't believe that after 4 versions this still hasn't been resolved... I'd say it's one of the most fundamental things when it comes to web development and SQL. Thanks anyway, maybe someone else will have a different solution. If not, I'd have to stick with 2 queries for the time being... — walther, May 23 '12 at 16:17

Keith Robertson · Accepted Answer · 2012-05-24T16:33:09.747

3

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

edited May 24 '12 at 16:33

answered May 23 '12 at 16:17

Keith Robertson

791
7
13

Just tried it, but it doesn't seem to do it the way I'd expect... Please, see my update, maybe you can explain. – walther May 23 '12 at 18:05
1

@walther. No, it **should** execute two queries, and that's okay!! What's important is that they are performed in a single send/receive from the DB server. The amount of time to process the 2 queries in the DB pales in comparison to the round-trip network delay. Besides, if you could get, `SELECT COUNT(id), ...` to work, it would only give you the count of rows on the requested page; I think you want the count of records matching the whole query, but you want only a subset (a single page) of the actual records. This is the recommended approach for querying a total count + a page of records. – Keith Robertson May 24 '12 at 16:23
Yes, I see... Even though there's a way using raw sql to make it a one query, I guess this is the best using the linq at the moment. Thanks. – walther May 24 '12 at 16:36
@walther. Perhaps, but I believe it would have to repeat the essential where clause -- once for computing the total count, once for obtaining the paged records -- in which case I don't see any advantage. If you know a way around this, please share! – Keith Robertson May 24 '12 at 16:50
I'm just curious about one more thing - how can you verify that the queries are performed in a single action at the SQL server? In the profiler it looks exactly the same as always... – walther May 24 '12 at 16:56
You could use a network sniffing tool like Wireshark to verify that there is one back-and-forth transaction with the DB, versus two without using Future Queries. – Keith Robertson Jul 16 '12 at 14:15

score 0 · Answer 2 · answered Jul 11 '19 at 11:56

0

this 'EntityFramework.Extended' library is no longer supported use this one instead: entityframework-plus and go here: https://entityframework-plus.net/query-future to see how you can get count and records in the same query.

answered Jul 11 '19 at 11:56

Ben.S

708
1
5
24

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

2 Answers2

Linked