LINQ Cookbook, Recipe 7: Selecting Pages of Data from Northwind (Jonathan Aneja)

VBTeam

Ingredients:

Categories: LINQ-To-Objects, LINQ-To-SQL

Instructions:

·         Create a new Windows Forms project with a Northwind DataContext (for step-by-step instructions on how to do this please see Recipe #6)

·         Drop a DataGridView control from the Toolbox onto the Form

·         Add the following line of code in your Form1_Load event:

            Dim db As New NorthwindDataContext

One of the key considerations when designing an application is how much data to show the user at a time.  For instance, if the user enters a query for products and there’s 10 million matches in the database, it’s probably not a good idea to display all those records on the screen at once. 

The typical solution for this is to show one “page” worth of data at a time, and then give the user the ability to move to the next page.  This results in a faster application which is easier for your customers to use.

In order to implement paging with LINQ, we’re going to use two new operators called Skip and Take.  For instance, consider the following query:

        Dim q = From p In db.Products _

                Select p.ProductID, p.ProductName, p.UnitPrice _

                Skip 20 _

                Take 20

This will select 3 fields from my Products table and ignore the first 20 rows, and give me the next 20, or to put it another way: page 2 of my results.  Now let’s look at a function we can use to generalize this to any number of rows:

    Public Function GetProductsPage(ByVal db As NorthwindDataContext, _

      & nbsp;                             ByVal pageNum As Integer, _

                                    ByVal pageSize As Integer) As IEnumerable

        Return From p In db.Products _

               Select p.ProductID, p.ProductName, p.UnitPrice _

               Skip (pageNum – 1) * pageSize _

               Take pageSize

    End Function

Note: When using Skip/Take you must ensure that you select any identity columns (ProductID in this case) when working with SQL Server 2000.  For SQL Server 2005 you don’t need to worry about this.

We can now use this function by passing in the page number and the size (i.e. number of rows in the page):

    Private Sub Form1_Load() Handles MyBase.Load

        Dim db As New NorthwindDataContext

        DataGridView1.DataSource = GetProductsPage(db, 3, 20)

    End Sub

VBTeam

Follow