Paginated search results with LINQ to SQL

Question

What's the best pattern to get paginated results with LINQ to SQL?

I have the following scenario:

Suppose I want to search items table by description. I can easily do:

public IQueryable<Item> FindItemsByDescription(string description)
{
   return from item in _dc.Items
          where item.Description.Contains(description);
}

Now, what would be the best way to paginate this result set?

Should I perform a count query before doing this to find out the result set size and then limit this query according to what I want? I feel like this is the way to go.
Should I perform the full query, take the count from the array size and return only a paginated subset from this array? I feel like this will be a huge waste of time if the resultset is big enough... Or is LINQ to SQL doing some magic here?

Is there a LINQ to SQL common pattern for performing this operation?

EDIT: I must clarify a one little thing. I am aware of Take and Skip methods. But, before using Take and Skip, how should I get the total count of results that query would retrieve?

Jose Basilio · Accepted Answer · 2009-04-27T14:46:40.717

33

The pattern for paging is very simple. It involves the use of the Skip() and Take() extension methods as follows:

public IQueryable<Item> FindItemsByDescription(string description, int pageIndex, int pageSize)
{
   return from item in _dc.Items
          where item.Description.
          Contains(description).
          Skip((pageIndex - 1) * pageSize).
          Take(pageSize);
}

UPDATE: To get the total count simply use the Count() method:

int totalCount = from item in _dc.Items
                 where item.Description.
                 Contains(description).Count();

int numberOfPages = (int)(totalCount/pageSize);

Depending on how you are going to the display the records, you can use the numberOfPages to display a navigation bar with "Page X of Y" ... Page 1 of 10, etc..

edited Apr 27 '09 at 14:46

answered Apr 27 '09 at 14:35

Jose Basilio

50,714
13
121
117

Pablo - I updated my post to include more information on paging. (Salu2) – Jose Basilio Apr 27 '09 at 14:47
Thanks a lot Basilio. Is this, performance wise, the best way to do it? Do you know how many queries will be performed against SQL server with this approach? – Pablo Santa Cruz Apr 27 '09 at 15:06
3

Pablo - The code in the example would perform 2 separate SQL queries. However, this does not have to be the case. You can take the results from the query and put it into a list by using ToList(), then get the Count() from that. So that you end up with only one call to the database. – Jose Basilio Apr 27 '09 at 15:17
4

"(int)(totalCount/pageSize)" is wrong. Suppose you have 5 items and the page size is 4? The page count will be 1 instead of 2. You also need to test whether totalCount%pageSize==0 or not and add another unit or not. – Andrei Rînea Apr 27 '09 at 19:50
4

for the numberOfPages, I used the Math.Ceiling method, like this `int numberOfPages = (int) Math.Ceiling((double) totalCount/pageSize);` – Alex Fairchild Jul 02 '13 at 19:09
Is there any gained in doing something like this? `var Queryobj = (from item in _dc.Items where item.Description.Contains(description)); int totalCount = Queryobj.Count(); ... return QueryObj.Skip((pageIndex - 1) * pageSize).Take(pageSize);` Would that run the where clause twice in the database and only return the count for the first bit and then paged values for the return? – Dan Jul 15 '13 at 21:07
@JoseBasilio that doesn't work because you want the total count of all matching rows, not just the count of the page. Now you could do a ToList() before pagination, but then you'd be unnecessarily loading your entire result and not just the page which would have massive performance overhead. – Austin Salgat Aug 09 '21 at 12:50

score 1 · Answer 2 · answered Apr 27 '09 at 14:29

You can use the Take extension method:

public IQueryable<Item> FindItemsByDescription(string description, int resultAmount)
{
   return from item in _dc.Items
          where item.Description.Contains(description).Take(resultAmount);
}

You can take this one step further and use Skip for subsequent "pages":

public IQueryable<Item> FindItemsByDescription(string description, int resultAmount, int page)
{
   return from item in _dc.Items
          where item.Description.Contains(description).Skip(resultAmount * page).Take(resultAmount);
}

Thanks a lot for the quick answer. I am aware of Take and Skip methods exitence. However, if I really want to paginate I must know what's the total number of results without paginating. So, what's the best way to get that *total number*? — Pablo Santa Cruz, Apr 27 '09 at 14:35

Paginated search results with LINQ to SQL

2 Answers2

Linked