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

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

  • Comments 8

 

Ingredients:

-          Visual Studio 2008 (Beta2 or Higher)

-          Connection to Northwind database

 

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, _

                                    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

Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • Not keen on the requirement ot have Strict Off for this example.  

    Apart from that, it's cool to see the SQL this generates:

    http://msmvps.com/blogs/bill/archive/2007/07/13/inside-recipe-7.aspx

  • Paging rant:

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

    You forgot to mention the "typical solution" (99.99%) runs actually counter to the faster application and easier to use assertion.

    Assume a 100 page word document, modify Word so that each page is fetched separately from the source location (say a shared cd over network to give it some Internet like latency). Now add a button that must be clicked to get to next and previous page. Don't give ability for going to specific page in one click. Limit find/seach queries to only happen once every x minutes per user to keep the remote resource available.

    Now there's your typical database app in the real world. It's much more server resource intensive, much less user friendly etc.

    So please don't ever give samples of how to do paging in naive way since it's just bad for every party involved.

    In most cases the queries aren't that complex that it makes any difference to retrieve 1 page of records (20) rather than say 6+ pages at once to get rid of the next/previous button approach of browsing the data. If your server dies to retrieving 6+ at once vs 1 then you have issue in database design.

    Obviously doing paging properly quickly becomes a very very complex problem, one that even Adobe hasn't solved properly yet if you look at their PDF streaming - it's just not as good as it could be yet. And Microsoft hasn't even attempted to solve it - take a look at their forums - all use the naive approach.

  • Already covered this solution 3 months ago in a object agnostic way a few weeks ago. Yours is centered on a product type which is only as useful as the product entity is. Why rewrite what you can use over and over again:

    http://foreachdev.net/blog/2007/04/29/linq-paging/

  • Joku,

    U have no clue what your talking about.  6 pages of data...LOL.  Try writing a Business Intelligence application when your talking about searching/filtering/paging 400,000 records...you change your opinion very quickly on how much data is acceptable to be sent over the wire.

    Paging is not that complex, there are many samples of custom paging controls and even in VS 2008 Orcas there is a DataPager control all ready to be customized and AJAX enabled for a "user friendly" set up.

  • Hi,

    I can't figure out how to write a dynamic orderby ( sortColumns ) statement. Please assit me !

    Regards,

    Luc

    <System.ComponentModel.DataObject(True)> _

    Public Class ProductFacade

       Dim DataContext As New DataClasses1DataContext

       <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select)> _

       Public Function GetAllProducts(ByVal sortColumns As String, ByVal maximumRows As Int32, ByVal startRowIndex As Int32) As IEnumerable(Of Product)

           Return From p In DataContext.Products.Skip(startRowIndex).Take(maximumRows)

       End Function

       Function GetAllProductsCount(ByVal sortColumns As String, ByVal maximumRows As Int32, ByVal startRowIndex As Int32) As Integer

           Return DataContext.Products.Count

       End Function

    End Class

  • You speak at the beginning from Visual Studio 2008 Beta 2 - then it will be published?

    Thanks for the information.

  • Is there a C# equivalent to skip/take?

  • Ralf - You can download Beta2 from http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx

    Adam - C# supports Skip/Take through extension method syntax, but it doesn't have query comprehensions for these operators.

    Hope that helps,

    Jonathan

Page 1 of 1 (8 items)