When using skip and take to page data, how can I get the total record count without a separate query?

Question

I don't see how this is possible, but I really, really hate to run my query an extra time just to get the record count so I can build a pager. When I say a "pager" I simply mean the common gizmo with a link for each 10 records for example.

Answer 1

As far as I know it is not possible to return the total count and the items retrieved by skip and take at the same time.

Answer 2

Assuming you are building a query in the selecting event, the best you could do is construct the full query, get and save the count, then take or skip it into the e.result.

And by best I mean, the easiest read code from a single query, rather than two. You'll still be running two separate evaluations on the database though. Use query analyser to see if the statements are a 'Select Count' then a 'Select take' or a dirty big select pared down by LINQ after the retrieve. I think LINQ does the former.

Answer 3

I wrote a custom data source control and view, which caches the count for a short duration. I invalidate the cache whenever the criteria changes that would affect the number of results, but not when the data is paged, or when the data is sorted for instance.