Linq query with multiple OrderBy statements added in a loop

linq, sql-order-by, vb.net

Solution

You could write your own extension method `OrderByOrThenBy` which checks whether the value is already an `IOrderedQueryable`, uses `ThenBy` if so and `OrderBy` otherwise. Slightly smelly, but not terribly hard to do.

EDIT: C# sample (untested):

public static class QueryableOrdering
{
    public static IOrderedQueryable<TElement> OrderByOrThenBy<TElement, TKey>
        (this IQueryable<TElement> source,
         Expression<Func<TElement, TKey>> ordering)
    {
        if (source == null)
        {
            throw new ArgumentNullException("source");
        }
        if (ordering == null)
        {
            throw new ArgumentNullException("ordering");
        }
        var ordered = source as IOrderedQueryable<TElement>;
        return ordered == null ? source.OrderBy(ordering)
                               : ordered.ThenBy(ordering);
    }
}

Problem

I have a method in a webservice that has parameter with which users can decide how they want to order their results. This is a `List(Of String)` with the names of the fields in the order they want to sort them. I know I can normally order on multiple columns by doing the following ``` Dim test = Bars.OrderBy(Function(x) x.Foo) _ .ThenBy(Function(x) x.Bar) _ .ThenBy(Function(x) x.Test) ``` However in this case this won't work since I can't chain the `ThenBy` function because I'm adding the sorting orders in a loop. To use `ThenBy` I need an `IOrderedQueryable` collection. This is how I would want it to work ``` Dim sortColumns = {"Foo", "Bar", "Test"} Dim query = From b in Bars For each column in sortColumns Select Case column Case "Foo" query = query.Orderby(Function(x) x.Foo) Case "Bar" query = query.Orderby(Function(x) x.Bar) Case "Test" query = query.Orderby(Function(x) x.Test) End Select Next Dim result = query.Select(Function(x) x.x).ToList() Return result ``` This of course won't work because `OrderBy` will replace any previous ordering. The only solution I can think of is ordering the list on some other variable first so I already have an `IOrderedQueryable` collection but this just seems like the wrong approach. ``` Dim bars As New List(Of Bar) Dim sortColumns = {"Foo", "Bar", "Test"} Dim query = bars.Select(Function(x) New With {.Temp = 1, .x = x}) _ .OrderBy(Function(x) x.Temp) For Each column In sortColumns Select Case column Case "Foo" query = query.ThenBy(Function(x) x.x.Foo) Case "Bar" query = query.ThenBy(Function(x) x.x.Bar) Case "Test" query = query.ThenBy(Function(x) x.x.Test) End Select Next Dim result = query.Select(Function(x) x.x).ToList() Return result ```

Original source