Effective Paging Using Linq in ASP.NET

Effective Paging Using Linq (Sample Code)

Binding GridView (or any other data control) is very easy using Linq. It is very easy efficient through LinQ two operators skip and take made this task very easy. How we achieved effective paging from Linq on server side? If you guys are familiar with linq and its techniques then you must read about Deferred Execution of Linq. Deferred Execution in linq depicts that linq executes query when is about to use, means when we are using var of linq not when we write it .learn more about Deferred Execution.

Skip and Take operators allows you to only pull back the records you need.

If you guys are not interested to read this Article you guys can scroll Down to end of this article there you will get sample code of this article.We can Achieve Effective paging by following methods.

1. Stored procedure
2. From linq
3. Many Magical tool available on the internet :P :P :D

In my sample code you will get all above mentioned ways except last one. In the simple example below i am using a LinqDataSource and handling its onselecting method to create our LINQ query and achieving effective paging .Set AutoPage to false because i am writing code to handle paging ourselves. Also the PageSize property of the GridView control is being populated from a integer constant in the code-behind class.
On Aspx page-

<asp:GridView AllowPaging="True" AutoGenerateColumns="False" DataSourceID="LinqDataSource1"

            ID="GridView1" PageSize="<%# PAGE_SIZE %>" runat="server" Width="276px">

            <Columns>

                <asp:BoundField DataField="ProductID" HeaderText="PID" ReadOnly="True" SortExpression="ProductID">

                    <asp:boundfield datafield="ProductName" headertext="ProductName" readonly="True"

                        sortexpression="ProductName">

                        <asp:boundfield datafield="UnitPrice" headertext="UnitPrice" readonly="True" sortexpression="UnitPrice">

                        </asp:boundfield>

                    </asp:boundfield>

                </asp:BoundField>

            </Columns>

        </asp:GridView>

        <asp:LinqDataSource AutoPage="False" ContextTypeName="DataClassesDataContext" EntityTypeName=""

            ID="LinqDataSource1" OnSelecting="LinqDataSource1_Selecting" runat="server" Select="new (ProductID, ProductName, UnitPrice)"

            TableName="Products">

        </asp:LinqDataSource>

On Code Behind:

 

DataClassesDataContext db = new DataClassesDataContext();

protected void Page_Load(object sender, EventArgs e)

{

    if (!IsPostBack)

    {

    }

}

public const int PAGE_SIZE = 10;

protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)

{

    // LINQ query

    var query = from m in db.Products

    select m;

    // 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(GridView1.PageIndex * PAGE_SIZE).Take(PAGE_SIZE);

    e.Result = query;

}

Download Sample Code From Here for Linq effective paging and paging from stored procedure .