How to create Expression Tree for multiple column OrderBy Expression

Question

I have create an orderby expression for my EF generic Repository as following string command = orderByDesc ? "OrderByDescending" : "OrderBy";

var type = typeof(T);

var property = type.GetProperty(orderby);

var parameter = Expression.Parameter(type, "p");

var propertyAccess = Expression.MakeMemberAccess(parameter, property);

var orderByExpression = Expression.Lambda(propertyAccess, parameter);

var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },

                       items.Expression, Expression.Quote(orderByExpression));
items = items.Provider.CreateQuery<T>(resultExpression);

Now I want to create the Expression with 2 columns for ordering and wasn't able to find out something helpful.

Please help me to create an orderby expression with 2 columns.

Answer 1

The below extension method for the IQueryable worked out for me:

public static IQueryable<TEntity> OrderByColumns<TEntity>(
       this IQueryable<TEntity> collection,
       List<Sort> sortedColumns)
    {
        if (sortedColumns.Any())
        {
            var parameter = Expression.Parameter(typeof(TEntity));

            foreach (var sortedColumn in sortedColumns)
            {
                var prop = Expression.Property(parameter, sortedColumn.Name);

                var exp = Expression.Lambda(prop, parameter);
                string method = String.Empty;
                if (sortedColumns.First() == sortedColumn)
                {
                    method = sortedColumn.SortOrder == SortOrder.Ascending
                        ? "OrderBy"
                        : "OrderByDescending";
                }
                else
                {
                    method = sortedColumn.SortOrder == SortOrder.Ascending
                        ? "ThenBy"
                        : "ThenByDescending";
                }
                var orderbyExpression = Expression.Call(typeof(Queryable), method, new Type[] { typeof(TEntity), exp.Body.Type },
                  collection.Expression, exp);
                collection = collection.Provider.CreateQuery<TEntity>(orderbyExpression);
            }
        }
        return collection;
    }
}

Answer 2

I don't get how the accepted answer is the accepted answer because the OP was asking how to create Expression Tree for multiple column sorting scenario.

Sometimes you have to build the OrderBy statement manually using Expression Tree because you have no idea what columns the user wants to sort. For example, when you have a grid built using Datatables and some of the columns are sortable, the user can SHIFT click on the column headers to sort by multiple columns:

The screenshot shows the user wants to sort the grid by Cassette (which is a string) and Slot Number (which is a double).

OrderBy* / ThenBy*

The tricky part here when building an Expression Tree for ordering is that for the first time you need to use OrderBy*, but for second time and later, you need to switch to use ThenBy*.

I will demonstrate how I do it with the extension method on IQueryable:

namespace DataTables.AspNet.Core
{
    public interface ISort
    {
        int Order { get; }
        SortDirection Direction { get; }
    }

    public enum SortDirection
    {
        Ascending = 0,
        Descending = 1
    }
}

namespace DL.SO.Framework.Mvc.DataTables.Extensions
{
    public static class QueryableExtensions
    {
        public static IQueryable<TModel> OrderByColumns<TModel>(
            this IQueryable<TModel> collection, 
            IDictionary<string, ISort> sortedColumns)
        {
            // Basically sortedColumns contains the columns user wants to sort by, and 
            // the sorting direction.
            // For my screenshot, the sortedColumns looks like
            // [
            //     { "cassette", { Order = 1, Direction = SortDirection.Ascending } },
            //     { "slotNumber", { Order = 2, Direction = SortDirection.Ascending } }
            // ]

            bool firstTime = true;

            // The type that represents each row in the table
            var itemType = typeof(TModel);

            // Name the parameter passed into the lamda "x", of the type TModel
            var parameter = Expression.Parameter(itemType, "x");

            // Loop through the sorted columns to build the expression tree
            foreach (var sortedColumn in sortedColumns.OrderBy(sc => sc.Value.Order))
            {
                // Get the property from the TModel, based on the key
                var prop = Expression.Property(parameter, sortedColumn.Key);

                // Build something like x => x.Cassette or x => x.SlotNumber
                var exp = Expression.Lamda(prop, parameter);

                // Based on the sorting direction, get the right method
                string method = String.Empty;
                if (firstTime)
                {
                    method = sortedColumn.Value.Direction == SortDirection.Ascending
                        ? "OrderBy"
                        : "OrderByDescending";

                    firstTime = false;
                }
                else
                {
                    method = sortedColumn.Value.Direction == SortDirection.Ascending
                        ? "ThenBy"
                        : "ThenByDescending";
                }

                // itemType is the type of the TModel
                // exp.Body.Type is the type of the property. Again, for Cassette, it's
                //     a String. For SlotNumber, it's a Double.
                Type[] types = new Type[] { itemType, exp.Body.Type };

                // Build the call expression
                // It will look something like:
                //     OrderBy*(x => x.Cassette) or Order*(x => x.SlotNumber)
                //     ThenBy*(x => x.Cassette) or ThenBy*(x => x.SlotNumber)
                var mce = Expression.Call(typeof(Queryable), method, types, 
                    collection.Expression, exp);

                // Now you can run the expression against the collection
                collection = collection.Provider.CreateQuery<TModel>(mce);
            }

            return collection;
        }
    }
}

Notes: OrderBy* means OrderBy Or OrderByDescending. Same for ThenBy*.

Answer 3

Ordering by multiple columns in LINQ works by calling OrderBy() followed by zero or more calls to ThenBy(). You can't do this using a single call to OrderBy().

For example, if you can want to sort by the columns a and b, you will have to generate an expression that looks something like:

items.OrderBy(p => p.a).ThenBy(p => p.b)