Jon's Blog

.NET Development & More

LinqDataSource: Efficient Custom Paging with LINQ

NOTE: If you set your LinqDataSource's AutoPage property to true and are using SQL Server (2005+ I believe) then it will automatically use Skip() and Take() as seen below. However, if you want to do it manually or wonder how it works then read on.

Creating a GridView (or other data control) with efficient paging is very easy with LINQ.  You can thank the Take and Skip operators for this; they allow you to only pull back the records you need.  In the simple example below we are using a LinqDataSource and handling its onselecting method to create our LINQ query and do our paging.  Set AutoPage to false since we are writing code to handle paging ourselves.  Also the PageSize property of the GridView control is being populated from a constant in the code-behind class.

ASPX:

<asp:LinqDataSource ID="linqDS" runat="server" AutoPage="false" 
    ContextTypeName="Namespace.MyDataContext" 
    onselecting="linqDS_Selecting" />
        
<asp:GridView ID="myGV" runat="server" DataSourceID="linqDS"
    AllowPaging="true" PageSize="<%# PAGE_SIZE %>"
    AutoGenerateColumns="false">
    <Columns>
        <!--Removed for simplicity -->
    </Columns>
</asp:GridView>

 

Code-behind:

// Const declared at top of code-behind
public const int PAGE_SIZE = 100;

protected void linqDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    // LINQ query
    var query = from c in myDC.Class
                select c;

    // Do advanced query logic here (dynamically build WHERE clause, etc.)     
    
    // Set the total count     
    // so GridView knows how many pages to create    
    e.Arguments.TotalRowCount = query.Count();

    // Get only the rows we need for the page requested
    query = query.Skip(myGV.PageIndex * PAGE_SIZE).Take(PAGE_SIZE);

    e.Result = query;
}

 

Comments (5) -

  • Mark

    11/15/2010 1:51:25 PM |

    Many thanks.  You saved my life.  I adapted slightly the code for VB and using a datapager to come up with:

    Protected Sub ListLinqDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) Handles LinqDataSource.Selecting

      Dim db As New dbDataContext

      Dim query = From p In db.mytbl
                  Select p

      e.Arguments.TotalRowCount = query.Count()
      e.Result = query.Skip(DataPager.StartRowIndex).Take(DataPager.MaximumRows)

    End Sub

  • Mark

    12/13/2010 4:56:16 AM |

    Update to my comment above.

    I am using a ListView and DataPager.  The above code worked correctly when I had one DataPager.  However, I decided to use two datapagers (one at the top and one at the bottom of my listview).  Then I had a problem as the above code only referenced one of the datapagers.  The solution was to change DataPager.StartRowIndex and DataPager.MaximumRows in the above code with e.Arguments.StartRowIndex and e.Arguments.MaximumRows.

  • Joe

    6/10/2011 11:25:28 AM |


    The LinqDataSource does this type of paging automatically when using sqlServer.

  • Val

    2/7/2013 1:42:30 AM |

    I think what Joe meant is that you do not need to use the Skip() and Take() methods in your query. LinqDataSource has AutoPage set to true by default, which does this automatically.

  • jonjj7

    2/15/2013 4:46:01 PM |

    Val/Joe,

    Thanks for pointing this out. I confirmed this by using SQL Server Profiler. I've added a note to the top of the post.

Comments are closed