LINQ to SQL and DataPager

Question

I'm using LINQ to SQL to search a fairly large database and am unsure of the best approach to perform paging with a DataPager. I am aware of the Skip() and Take() methods and have those working properly. However, I'm unable to use the count of the results for the datapager, as they will always be the page size as determined in the Take() method.

For example:

var result = (from c in db.Customers
              where c.FirstName == "JimBob"
              select c).Skip(0).Take(10);

This query will always return 10 or fewer results, even if there are 1000 JimBobs. As a result, the DataPager will always think there's a single page, and users aren't able to navigate across the entire result set. I've seen one online article where the author just wrote another query to get the total count and called that.

Something like:

int resultCount = (from c in db.Customers
                   where c.FirstName == "JimBob"
                   select c).Count();

and used that value for the DataPager. But I'd really rather not have to copy and paste every query into a separate call where I want to page the results for obvious reasons. Is there an easier way to do this that can be reused across multiple queries?

Thanks.

Answer 1

In situations like this I sometimes return the total record count as a field in my result set from the db.

Basically you only have the two options, write another query specifically for the count, or return it as column in the results.

Answer 2

Remember that linq provides defferred query execution...

var qry =     from c in db.Customers              
              where c.FirstName == "JimBob"
              select c;

int resultCount = qry.Count();

var results = qry.Skip(0).Take(10);