20

To get a page from a database I have to execute something like this:

var cs = ( from x in base.EntityDataContext.Corporates
   select x ).Skip( 10 ).Take( 10 );

This will skip the first 10 rows and will select the next 10.

How can I know how many rows would result because of the query without pagination? I do not want to run another query to get the count.

R. Martinho Fernandes
  • 228,013
  • 71
  • 433
  • 510
effkay
  • 794
  • 2
  • 17
  • 36

2 Answers2

31

To get the total number of records before skip/take you have to run a separate query. Getting the actual number returned would use Count(), but wouldn't result in another query if the original query was materialized.

var q = from x in base.EntityDataContext.Corporates 
        select x;

var total = q.Count();
var cs = q.Skip(10).Take(10);
var numberOnSelectedPage = cs.Count();
Ruben Bartelink
  • 59,778
  • 26
  • 187
  • 249
tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • Is this separation of 2 query results in lower perfomence for lots of data and complex where conditions? – Mayur Patel Feb 11 '16 at 10:59
  • We are looking for something equivalent to COUNT(1) OVER(). like in sql i can write this query: SELECT name, overall_count = COUNT(1) OVER() FROM sys.all_objects ORDER BY name OFFSET (@PageNum-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY; Can I write the same with linq/EF? – VISHMAY Nov 05 '19 at 10:33
17

Bottom line: you have to run two queries. You simply can't get around it.

Here's a good way to do it, however, that caches the original LINQ query and filter, making for less copy/paste errors:

var qry = from x in base.EntityDataContext.Coporates select x;
var count = qry.Count();
var items = qry.Skip(10).Take(10).ToList();
Randolpho
  • 55,384
  • 17
  • 145
  • 179