CE's Blog

Implementing custom paging with Linq and ObjectDatasource allows us to build data access layers which deliver good performance on large data while still keeping a modular and easy to maintain business object structure.
Since the data request pattern for custom paging is different from query patterns which deliver a ‘complete’ data set, additional functionality has to be implemented.

The following examples assume an ObjectDataSource in connection with an ASP.NET GridView control. When using custom paging, every DataBind() on the GridView results in two calls:

1. First, the call to the data retrieval method with selection and sort parameters plus a ‘Skip’ cound and a ‘Limit’ count to restrict the number of records returned.
2. Second, a call to a method which delivers the total amount of records available (so the control knows how many pages there are. This method also needs to receive the same selection parameters as the first method.

Basically the same filtering has to be performed in both methods, whereas the first method returns an IEnumerable, and the second returns a single integer. With an increasing number of selection parameters, a lot of redundant functionality needs to be added to both methods. So the question is, if the filtering could be performed in a single method which is then accessed by method 1 and 2.

This is easily possible if the result of the query is an existing type. Linq defered execution will enable reusing of the filtering expression for different purposes. However if the expression returns an anonymous type as an IQueryable, further processing of that type becomes more difficult.

Basically the problem is that a non-generic IQueryable is not supported by the Linq extension methods such as Count(), Skip(), Take(), OrderBy(), OrderByDescending(). Also the extension method ToArray() is not supported for a non-generic IEnumerable.

So by finding a way to implement those missing extension methods, we could save ourself implementation work and eliminate redundancies at the same time.

The following extension methods provide the missing functionality that will also work with non-generic IQueryable and IEnumerable and thus supports anonymous types.

/// This helper class will provide several Linq extension methods for sorting and custom paging.
public static class LinqExtensions
{
    /// Method used for sorting according to the pseudo SQL syntax emmitted by ObjectDataSource.
    public static IQueryable OrderBySQLSyntax( this IQueryable source, string sSQLOrder )
    {
        string[] asOrder = sSQLOrder.Split( new char[] { ' ' }, 2 );
        bool descending = ( asOrder.Length == 2 && String.Equals( asOrder[ 1 ], "DESC" ) );
        return OrderByExtension( source, asOrder[ 0 ], descending );
    }
 
    /// Used for sorting in ascending/descending order according to the property provided.
    public static IQueryable OrderByExtension( this IQueryable source, string propertyName, bool descending )
    {
        ParameterExpression x = Expression.Parameter( source.ElementType, "x" );
        LambdaExpression selector = Expression.Lambda( Expression.PropertyOrField( x, propertyName ), x );
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), descending ? "OrderByDescending" : "OrderBy",
            new Type[] { source.ElementType, selector.Body.Type }, source.Expression, selector );
        return source.Provider.CreateQuery( mce );
    }
 
    /// Adds a count expression to the query.
    public static int CountExtension( this IQueryable source )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Count",
            new Type[] { source.ElementType }, source.Expression );
        return source.Provider.Execute<int>( mce );
    }
 
    /// Converts the IEnumerable to an array. The main purpose is to stop the defered execution chain.
    public static IEnumerable ToArrayExtension( this IQueryable source )
    {
        MethodCallExpression mce = Expression.Call( typeof( Enumerable ), "ToArray",
            new Type[] { source.ElementType }, source.Expression );
        return source.Provider.Execute<IEnumerable>( mce );
    }
 
    /// Adds custom paging support to an IQueryable.
    public static IQueryable SkipExtension( this IQueryable source, int iSkip )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Skip",
            new Type[] { source.ElementType }, new Expression[] { source.Expression, Expression.Constant( iSkip ) } );
        return source.Provider.CreateQuery( mce );
    }
 
    /// Adds custom paging support to an IQueryable.
    public static IQueryable TakeExtension( this IQueryable source, int iTake )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Take",
            new Type[] { source.ElementType }, new Expression[] { source.Expression, Expression.Constant( iTake ) } );
        return source.Provider.CreateQuery( mce );
    }
}

If the above appears to be too complicated, there is an alternative approach, which will be based on the fact that ObjectDataSource will call the data retrieval method first, and then call the count method. Therefore we could actually cache the value for the Count() method when the select method is called. This would also have a positive impact on performance. I am not sure though, if this order of calling can be relied on. Caching cannot be done in an instance variable, since ObjectDataSource apparently creates a new instance upon every call. The place where it makes most sense is withing the HttpContext.Current.Items location inside web applications. In case you are using several ObjectDataSources on one page you have to make sure the parameter name for the cached value is unique, so there are no collisions. See my next post for details.