Using the Entity Framework and the ObjectDataSource: Custom Paging

Readers maybe familiar with the Entity Framework (EF) tutorials on the asp.net web site (http://www.asp.net/entity-framework/tutorials). The latest addition to the series “Continuing with the Entity Framework” is an excellent tutorial on using version 4 of the Entity Framework in a web forms application with the ObjectDataSource control. However, at present there is one omission that prevents it from effective use on a web site where you need to list a large amount of data on a web page, and that is custom paging. With the author’s permission of the original tutorial I have posted this article on implementing custom paging based on his original code.

If you simply set AllowPaging to true on the GridView without implementing custom paging on the ObjectDataSource you will be moving large amounts of data from the database to the application in order to show one page of data and throw away the rest. Custom paging allows you to retrieve just the page of data you need from the database. Whilst it is a small overhead when you have a small amount of data that spans only one or two pages it quickly becomes performant as your data spans dozens, hundreds or thousands of pages.

This article will show you how to take the code that is used within the tutorial at Using the Entity Framework and the ObjectDataSource Control, Part 1: Getting Started and add custom paging support to the departments page.

Add two new methods to the repository interface contained in ISchoolRepository.cs:

IEnumerable<Department> GetDepartmentsByNamePaged(string sortExpression, string nameSearchString, int startRowIndex, int maximumRows);
int GetDepartmentsByNameCount(string nameSearchString);

GetDepartmentsByNamePaged extends the GetDepartmentsByName method to take parameters for the start point and the number of records to return in a page. GetDepartmentsByNameCount is used to return the total number of records that would be returned after applying the search string which is needed to determine how many pages there are in total.

The names of the two last parameters in GetDepartmentsByNamePaged are a case of convention over configuration. These are the default names used by the ObjectDataSource for the parameters and by using these names it saves us from having to define/change them in properties of the ObjectDataSource.

Add the following implementation for the two new methods in the repository class contained in SchoolRepository.cs:

public IEnumerable<Department> GetDepartmentsByNamePaged(string sortExpression, string nameSearchString, int startRowIndex, int maximumRows)
{
    if (String.IsNullOrWhiteSpace(sortExpression))
    {
        sortExpression = "Name";
    }
    if (String.IsNullOrWhiteSpace(nameSearchString))
    {
        nameSearchString = "";
    }
    return context.Departments.Include("Person").Include("Courses")
        .OrderBy("it." + sortExpression)
        .Where(d => d.Name.Contains(nameSearchString))
        .Skip(startRowIndex)
        .Take(maximumRows)
        .ToList();
}

public int GetDepartmentsByNameCount(string nameSearchString)
{
    if (String.IsNullOrWhiteSpace(nameSearchString))
    {
        nameSearchString = "";
    }
    return context.Departments
        .Where(d => d.Name.Contains(nameSearchString))
        .Count();
}

Note how Skip and Take are used in the LINQ statement to return just the page of data you need.

In order to allow the solution to build you also need to implement the new methods in the interface in the test repository contained in MockSchoolRepository.cs as follows:

public IEnumerable<Department> GetDepartmentsByNamePaged(string sortExpression, string nameSearchString, int startRowIndex, int maximumRows)
{
    return departments
        .Skip(startRowIndex)
        .Take(maximumRows);
}

public int GetDepartmentsByNameCount(string nameSearchString)
{
    return departments
        .Count();
}

As we are using a separate business layer and calling that from our ObjectDataSource we need to implement methods there to call our new methods in the repository. Add the following code to SchoolBL.cs:

public IEnumerable<Department> GetDepartmentsByNamePaged(string sortExpression, string nameSearchString, int startRowIndex, int maximumRows)
{
    return schoolRepository.GetDepartmentsByNamePaged(sortExpression, nameSearchString, startRowIndex, maximumRows);
}

public int GetDepartmentsByNameCount(string nameSearchString)
{
    return schoolRepository.GetDepartmentsByNameCount(nameSearchString);
}

Now we can finally make changes to our web page to use the new functionality by editing the Department.aspx page. Change the markup for the DepartmentsObjectDataSource so that the SelectMethod property is set to GetDepartmentsByNamePaged, the EnablePaging property is set to true, and the SelectCountMethod property is set to GetDepartmentsByNameCount generic for premarin. The resultant markup for the DepartmentsObjectDataSource should look something like this:

<asp:ObjectDataSource ID="DepartmentsObjectDataSource" runat="server"  
    TypeName="ContosoUniversity.BLL.SchoolBL" DataObjectTypeName="ContosoUniversity.DAL.Department"  
    SelectMethod="GetDepartmentsByNamePaged" DeleteMethod="DeleteDepartment" UpdateMethod="UpdateDepartment" 
    ConflictDetection="CompareAllValues" OldValuesParameterFormatString="orig{0}"  
    OnUpdated="DepartmentsObjectDataSource_Updated" SortParameterName="sortExpression"  
    OnDeleted="DepartmentsObjectDataSource_Deleted" SelectCountMethod="GetDepartmentsByNameCount" EnablePaging="True"> 
    <SelectParameters> 
        <asp:ControlParameter ControlID="SearchTextBox" Name="nameSearchString" PropertyName="Text" 
            Type="String" /> 
    </SelectParameters> 
</asp:ObjectDataSource>

Change the markup for the DepartmentsGridView to set the AllowPaging property to true and the PageSize property to 2. We are setting the PageSize to 2 so that we can easily see the results of the paging without having to create lots of departments. The markup for the first part of the DepartmentsGridView should look something like this:

<asp:GridView ID="DepartmentsGridView" runat="server" AutoGenerateColumns="False" 
    DataSourceID="DepartmentsObjectDataSource"  
    DataKeyNames="DepartmentID,Name,Budget,StartDate,Administrator"  
    OnRowUpdating="DepartmentsGridView_RowUpdating" 
    OnRowDataBound="DepartmentsGridView_RowDataBound" 
    AllowSorting="True" AllowPaging="True" PageSize="2">

Then in the Design View for the web page, double-click on the SearchTextBox to create a TextChanged event and add the following code to it:

protected void SearchTextBox_TextChanged(object sender, EventArgs e)
{
    DepartmentsGridView.PageIndex = 0;
    DepartmentsGridView.Sort("Name", SortDirection.Ascending);
}

This event handler protects us from a situation where we are viewing a high page number and we add a search that will result in less pages than the page number we are currently viewing. Every time you change the search text it will reset the sort order and display the first page of results.

Now build and run the code to see the effects of your changes.