Programmatically chain OrderBy/ThenBy using LINQ / Entity Framework

Question

I have a reporting interface where the end user gets to select multiple fields for the sort order of the returned report. The problem I am having is that I can't really chain the OrderBy / ThenBy methods, since I'm iterating through a list of sort fields. I'm thinking something like this:

foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            query = query.ThenBy(l => l.RegionCode);
            break;
        case "type":
            query = query.ThenBy(l => l.Type);
            break;
        case "color":
            query = query.ThenBy(l => l.Color);
            break;
        case "category":
            query = query.OrderBy(l => l.Category);
            break;
    }
}

(Note: I've removed the switch determining if this is the first sort item for simplicity's sake.)

Any thoughts on how to iterate through a collection to determine the sort order?

Answer 1

You could do what you want if you use an initial "seed" OrderBy:

EDIT you need to call OrderBy to create an IOrderedEnumerable (or IOrderedQueryable) first before attaching ThenBy clauses:

var orderedQuery = query.OrderBy(l => 0);
foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            orderedQuery = orderedQuery.ThenBy(l => l.RegionCode);
            break;
        case "type":
            orderedQuery = orderedQuery.ThenBy(l => l.Type);
            break;
        case "color":
            orderedQuery = orderedQuery.ThenBy(l => l.Color);
            break;
        case "category":
            orderedQuery = orderedQuery.ThenBy(l => l.Category);
            break;
    }
}
query = orderedQuery;  // cast back to original type.

If you want something more flexible check out this answer

Answer 2

I've created these extension methods to tackle an identical problem as stated in the question:

public static class QueryableExtensions
{
    public static IOrderedQueryable<T> AppendOrderBy<T, TKey>(this IQueryable<T> query, Expression<Func<T, TKey>> keySelector)
        => query.Expression.Type == typeof(IOrderedQueryable<T>)
        ? ((IOrderedQueryable<T>) query).ThenBy(keySelector)
        : query.OrderBy(keySelector);

    public static IOrderedQueryable<T> AppendOrderByDescending<T, TKey>(this IQueryable<T> query, Expression<Func<T, TKey>> keySelector)
        => query.Expression.Type == typeof(IOrderedQueryable<T>)
            ? ((IOrderedQueryable<T>)query).ThenByDescending(keySelector)
            : query.OrderByDescending(keySelector);
}

The code in the question could then be refactored to:

foreach (string sort in data.SortParams)
{
    switch (sort)
    {
        case "state":
            query = query.AppendOrderBy(l => l.RegionCode);
            break;
        case "type":
            query = query.AppendOrderBy(l => l.Type);
            break;
        case "color":
            query = query.AppendOrderBy(l => l.Color);
            break;
        case "category":
            query = query.AppendOrderBy(l => l.Category);
            break;
    }
}

REMARK These extension methods only check the previous expression in the expression tree to determine wether to use OrderBy or ThenBy, no other expressions are allowed in-between. If you also want to tackle that, you'll have to walk through the complete tree which might just add that overhead you don't want :)