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.
- Stored procedure
- From linq
- 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 .