LINQ: Paging technique, using take and skip but need total records also - how to implement this?

Question

I have implemented a paging routine using skip and take. It works great, but I need the total number of records in the table prior to calling Take and Skip.

I know I can submit 2 separate queries.

1. Get Count
2. Skip and Take

But I would prefer not to issue 2 calls to LINQ.

How can I return it in the same query (e.g. using a nested select statement)?

Previously, I used a paging technique in a stored procedure. I returned the items by using a temporary table, and I passed the count to an output parameter.

Solution

I'm sorry, but you can't. At least, not in a pretty way.

You can do it in an unpretty way, but I don't think you like that:

var query = from e in db.Entities where etc etc etc;

var pagedQuery = 
    from e in query.Skip(pageSize * pageNumber).Take(pageSize)
    select new
    {
        Count = query.Count(),
        Entity = e
    };

You see? Not pretty at all.

Comments

[Enigmativity]

It's still two queries to the database. It only looks like one query if you squint hard enough at the LINQ query. ;-)

[Steven]

In fact, this code does in fact not result in a database query at all. Only if you iterate over the pagedQuery, the database will be called. It also depends on the LINQ provider, but a good provider will run this as one single databsae query. Some providers might execute N + 1 queries. Besides, executing two separate queries (a count and a results query) will probably have better performance than this thing. As I said, it aint pretty.

[Martin]

thanks steven! well maybe its not pretty but i like it :-) .. Do you think linq2sql would run 1 query or use 2? Or do you think it would be a real performance problem to infact just do 2 queries? i.e. get my count and store in variable and then new query to do my take and skip

[Steven]

L2S will definitely not run 2 queries. It will either run 1 query, or it will run N + 1 queries, which means that while iterating the pagedQueries it will execute the query.Count() again, and again, and again... Even when L2S is smart enough to run a single query, I think that SQL Server will handle two simple queries (count + result) faster than this single more complex one, but you'll have to try. Running the count query separate is the usual thing to do and I don't see a problem with this. If you need the last bit of performance, perhaps you should try something else (such as caching).

[Ahmed Magdy]

I think the right query is query.Skip(pageSize * pageNumber).Take(pageSize)

[VulgarBinary]

You do the skip / take on item = job.Skip(#).Take(#). Down vote for a valid answer which can even still solve your situation is uncalled for.

[Bart Calixto]

Answer should reflect with skip/take as the op. It does no answer op question. -1