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?
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?
"As long as LINQ to SQL lives under Entity Framework, it's dead.
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.
We can implement the paging using the Linq Skip and Take method.
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..
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); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With