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

I don't see how this is possible, but I really, really hate to run my query an extra time just to get 开发者_如何学Pythonthe 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.

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.

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.

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.

I was concerned about this same question. Here is are the results of my experimenting in Linqpad--the actual behavior of Linq does not create a full new query to the SQL server:

This simple test, in one of my development databases:

var query = from p in HrPersons select p;

var x = query.Skip(20).Take(10).Dump();

var t = query.Count().Dump();

generates the following actual SQL queries:

SELECT [t1].[company] AS [Company], [t1].[processLevel] AS [ProcessLevel], [t1].[emplId] AS [EmplId], [t1].[sn] AS [Sn], [t1].[givenName] AS [GivenName], [t1].[middleInitial] AS [MiddleInitial], [t1].[nickName] AS [NickName], [t1].[formerName] AS [FormerName], [t1].[ssn] AS [Ssn], [t1].[cn] AS [Cn], [t1].[costCenter] AS [CostCenter], [t1].[title] AS [Title], [t1].[status] AS [Status], [t1].[batch] AS [Batch], [t1].[rowversion] AS [Rowversion], [t1].[id] AS [Id], [t1].[source] AS [Source]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[company], [t0].[processLevel], [t0].[emplId], [t0].[sn], [t0].[givenName], [t0].[middleInitial], [t0].[nickName], [t0].[formerName], [t0].[ssn], [t0].[cn], [t0].[costCenter], [t0].[title], [t0].[status], [t0].[batch], [t0].[rowversion], [t0].[id], [t0].[source]) AS [ROW_NUMBER], [t0].[company], [t0].[processLevel], [t0].[emplId], [t0].[sn], [t0].[givenName], [t0].[middleInitial], [t0].[nickName], [t0].[formerName], [t0].[ssn], [t0].[cn], [t0].[costCenter], [t0].[title], [t0].[status], [t0].[batch], [t0].[rowversion], [t0].[id], [t0].[source]
    FROM [HrPerson] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
GO

SELECT COUNT(*) AS [value]
FROM [HrPerson] AS [t0]

So while there is a second SQL query, it is a trivial one that only requests the total count. I believe this is reasonable and acceptable as a pattern.