Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Paginated search results with LINQ to SQL

Tags:

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?

  1. 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.
  2. 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?

like image 463
Pablo Santa Cruz Avatar asked Apr 27 '09 14:04

Pablo Santa Cruz


People also ask

Is LINQ to SQL obsolete?

"As long as LINQ to SQL lives under Entity Framework, it's dead.

Is LINQ converted to SQL?

LINQ to SQL offers an infrastructure (run-time) for the management of relational data as objects. It is a component of version 3.5 of the . NET Framework and ably does the translation of language-integrated queries of the object model into SQL. These queries are then sent to the database for the purpose of execution.

Which LINQ methods should you use to implement pagination?

We can implement the paging using the Linq Skip and Take method.


2 Answers

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..

like image 60
Jose Basilio Avatar answered Oct 14 '22 13:10

Jose Basilio


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); } 
like image 44
BFree Avatar answered Oct 14 '22 13:10

BFree