GridView ObjectDataSource LINQ Paging and Sorting

If you've attempted to create your own Data Access Layer for LINQ and want to use the built-in data binding for .NET controls like a GridView, you know the nightmare of trying to get these controls to work together with LINQ new IQueryable interface. After much pain and suffering, I was able to cobble together little tidbits from various posts to create a solid object that can be used as a "codeless" ObjectDataSource for any GridView AND support sorting, paging, and filtering just like if you were to databind to a LINQ to SQL object (like EVERY tutorial shows you how to do).

The biggest help by far in getting to this point was a very complete post from Brian Orrell that you can read here:

http://borrell.parivedasolutions.com/2008/01/objectdatasource-linq-paging-sorting.html

First, create a new Database Context (.dbml) object using the fabulous LINQ O/R mapper tool built into Visual Studio 2008 (you can see how to do that here:

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Once you've created this, make a new class in your project and call it something like "ClassNameLinq.cs" or "ClassName.cs".

For convenience, I added a private property of my context object to use across my object called "DbContext". The declaration looks like this:

private Dal.DbContextDataContext m_DbContext = new Dal.DbContextDataContext();

public Dal.DbContextDataContext DbContext

{

    get { return m_DbContext; }

    set { m_DbContext = value; }

}

 

NOTE: I am using a simple database with a table called "Projects" that has a column called "NAME" (varchar 50) and a column called "PRIMARYKEY" (int).

Now implement the following methods:

You can't use an IQueryable object to data bind to a GridView and still use Paging and Sorting. You will get this really annoying exception: The data source does not support server-side data paging. So we create a method in our class that returns back the query as a List<> using LINQ ToList() method.

public List<Project> SelectAllList(string sSortType, int iBeginRowIndex, int iMaximumRows)

{

    var query = from q in DbContext.Projects

                       select q;

    // sort 

    query = SelectAllSort(query, sSortType);

    // filter the list if needed

    query = SelectAllQuery(query);

    // paginate

    query = query.Skip(iBeginRowIndex).Take(iMaximumRows);

    // execute the query and convert to list

    return query.ToList();

}

Next add a method for getting the query as an IQueryable object to do less typing and provide a way for other consumers of our class to use the LINQ syntax directly on the object (when not using a databound object situation).

private IQueryable<Project> SelectAllQuery(IQueryable<Project> query)

{

    // add filtering here if needed

    return query;

}

 

Now we need to implement the methods for sorting, pagination, and optionally filtering.

private IQueryable<Project> SelectAllSort(IQueryable<Project> query, string sSortType)

{

    bool bIsSortDescending = false;

    if (!String.IsNullOrEmpty(sSortType))

    {

        string[] sValues = sSortType.Split(' ');

        if (sValues.Length > 1)

        {

            if (sValues[1].ToUpper() == "DESC")

            {

                bIsSortDescending = true;

            }

        }

    }

    if (!String.IsNullOrEmpty(sSortType))

    {

        query = DbContext.Projects.OrderBy(sSortType);

    }

    else

    {

        // use a default sort here

        if (bIsSortDescending)

        {

            query = query.OrderByDescending(q => q.NAME);

        }

        else

        {

            query = query.OrderBy(q => q.NAME);

        }

    }

    return query;

}

 

One thing to note here is that you could manually create a specific sort query for each colum with desc/asc sorts but I chose to use a Dynamic LINQ query here instead because it was a LOT less coding and same speed in terms of performance BUT if you want to use this code as is, you need to download the free System.Linq.Dynamic library that MS distributes. You can get this library here:

http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx

Now create a method to get the count of the objects in your query (we need this so that pagination will work correctly).

public int SelectCount()

{

    var query = from q in DbContext.Projects

                       select q;

    query = SelectAllQuery(query);

    // execute the query and return the count

    return query.Count();

}

 

Now make a web form "GridTest.aspx" and drag a GridView control onto it. Use the wizard to create a new ObjectDataSource and point it to this new class we created for your DAL layer. For the "Select" method, point to the SelectAllList() method. Then click Finish (NOTE: there is more to configure but the wizard break's down at this point and doesn't let us configure things properly. We need to go to the HTML code).

Your ObjectDataSource1 should look like this:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"

    EnablePaging="true" 

    SelectCountMethod="SelectCount"

    SelectMethod="SelectAllList"

    SortParameterName="sSortType"

    MaximumRowsParameterName="iMaximumRows"

    StartRowIndexParameterName="iBeginRowIndex"

    TypeName="Dal.ProjectController">

</asp:ObjectDataSource>

Things to note here:

- EnablePaging = true (the source will handle the paging for us)

- SelectCountMethod = SelectAllCount (point this to the method that you created to get the count of the query)

- SelectMethod = SelectAllList (point this to our method in the new class that returns the subset of the query)

SortParameterName = sSortType (this is the name of the parameter for our SelectAllList() method)

MaximumRowsParameterName = iMaximumRows (this is the name of the parameter for our SelectAllList() method)

StartRowIndexParameterName = iBeginRowIndex (this is the name of the parameter for our SelectAllList() method)

Now configure your GridView manually to look like this:

<asp:GridView ID="GridView1" runat="server" 

    AllowPaging="True" 

    AllowSorting="True"

    EnableSortingAndPagingCallbacks="True"

    AutoGenerateColumns="False" 

    DataSourceID="ObjectDataSource1" 

    EmptyDataText="No rows to display.">

    <Columns>

        <asp:BoundField DataField="PRIMARYKEY" HeaderText="PRIMARYKEY" 

            SortExpression="PRIMARYKEY" />

        <asp:BoundField DataField="NAME" HeaderText="NAME" 

            SortExpression="NAME" />

    </Columns>

</asp:GridView>

 

Notice a couple of things:

- EnableSortingAndPagingCallbacks = true (so we can catch the postback events to do the sorting [which is handled by the ObjectDataSource1]).

- AllowPaging = true (the wizard blocks us from doing this)

- AllowSorting = true (the wizard blocks us from doing this)

Now run the web form and you have automatic pagination, sorting, and filter built in with literally no code written in the code-behind for this page. AND this object can be reused over and over. You could do this for each of your tables and joins, enjoy all the benefits of LINQ, etc.

One other note. Performance is better with this implementation than other LINQ Data Access Layer's I've seen on the next because pagination here uses the Take() method so we only ever return a subset of the total rows so this can handle VERY large tables.