Monday, September 24, 2012

LINQ OrderBy sorting without a Strong Typed parameter expression.

When setting up a simple ASPX page with a DataGrid to quickly display a LINQ to SQL result without having to write a bunch of paging and sorting code I ran into a bit of an issue. The loading and paging worked perfectly with the following sample but I could not get sorting to work and a clean and concise manner.

<asp:DataGrid ID="Data" runat="server" AllowPaging="True" AllowSorting="True" 
    onpageindexchanged="Data_PageIndexChanged" onsortcommand="Data_SortCommand">
</asp:DataGrid>
FormLandingPageRepository repo = new FormLandingPageRepository();

protected void Page_Load(object sender, EventArgs e)
{
    Data.DataSource = repo.Find().OrderByDescending(i => i.dateAdded);
    Data.DataBind();
}

protected void Data_PageIndexChanged(object source, DataGridPageChangedEventArgs e)
{
    Data.CurrentPageIndex = e.NewPageIndex;
    
    Data.DataSource = repo.Find().OrderByDescending(i => i.dateAdded);
    Data.DataBind();
}

protected void Data_SortCommand(object source, DataGridSortCommandEventArgs e)
{     
    repo.Find().OrderBy(e.SortExpression);
    Data.DataBind();
}

The problem turned out to be that the OrderBy operations in LINQ require strong types clauses in Expressions but my DataGrid is returning a simple string as the order by expression.  I could get around this by creating a switch statment and converting to a Strong Typed expression but that would require a lot of hard coded logic for every table.

Instead I found this Extensions after much searching and testing that adds the ability to do OrderBy statements in LINQ with a string value instead.  Thus after adding the Extension code the above sort by works as exacted. (Note that the sort expression will need to be stored in the ViewState and sorting logic applied in PageIndexChanged as well for Paging+Sorting to work.)

SortExstensions.cs
using System;
using System.Text.RegularExpressions;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

namespace JSP
{
    public static class SortExtensions
    {
        /// <summary>
        /// Sort Directions.
        /// </summary>
        enum SortMode
        {
            OrderBy,
            OrderByDescending,
            ThenBy,
            ThenByDescending
        }

        /// <summary>
        /// Sorts the elements of a sequence in ascending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string property)
        {
            // If this is a sort list then OrderBy the first element and then ThenBy the rest.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] {','}, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.OrderBy).ThenBy(parts[1]);
            }
                
            return ApplyOrder<T>(source, property, SortMode.OrderBy);
        }

        /// <summary>
        /// Sorts the elements of a sequence in descending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> OrderByDescending<T>(this IQueryable<T> source, string property)
        {
            // If this is a sort list then OrderBy the first element and then ThenBy the rest.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.OrderByDescending).ThenByDescending(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.OrderByDescending);
        }

        /// <summary>
        ///  Performs a subsequent ordering of the elements in a sequence in ascending order according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> ThenBy<T>(this IOrderedQueryable<T> source, string property)
        {
            // If ther are multiple sort items then sort by each in order.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.ThenBy).ThenBy(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.ThenBy);
        }

        /// <summary>
        ///  Performs a subsequent ordering of the elements in a sequence in descending order, according to a key.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        public static IOrderedQueryable<T> ThenByDescending<T>(this IOrderedQueryable<T> source, string property)
        {
            // If ther are multiple sort items then sort by each in order.
            if (property.Contains(','))
            {
                string[] parts = property.Split(new char[] { ',' }, 2);
                return ApplyOrder<T>(source, parts[0], SortMode.ThenByDescending).ThenByDescending(parts[1]);
            }

            return ApplyOrder<T>(source, property, SortMode.ThenByDescending);
        }

        /// <summary>
        /// Apply a custom Order By statment to an IQueryable.
        /// </summary>
        /// <typeparam name="T">The type of the elements of source.</typeparam>
        /// <param name="source">A sequence of values to order.</param>
        /// <param name="property">Property name to sort on.</param>
        /// <param name="methodName">Sort method to use.</param>
        /// <returns>An System.Linq.IOrderedQueryable<T> whose elements are sorted according to a key.</returns>
        static IOrderedQueryable<T> ApplyOrder<T>(IQueryable<T> source, string property, SortMode methodName)
        {
            string[] props = property.Split('.');

            Type type = typeof(T);
            ParameterExpression arg = Expression.Parameter(type, "x");
            Expression expr = arg;

            foreach (string prop in props)
            {
                PropertyInfo pi = type.GetProperty(prop);
                expr = Expression.Property(expr, pi);
                type = pi.PropertyType;
            }

            Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
            LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);

            object result = typeof(Queryable).GetMethods().Single(
                    method => method.Name == methodName.ToString("g")
                            && method.IsGenericMethodDefinition
                            && method.GetGenericArguments().Length == 2
                            && method.GetParameters().Length == 2)
                    .MakeGenericMethod(typeof(T), type)
                    .Invoke(null, new object[] { source, lambda });
            return (IOrderedQueryable<T>)result;
        }
    }
}

Friday, September 7, 2012