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?

D Stanley · Accepted Answer · 2017-11-06T15:35:47.343

20

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

edited Nov 06 '17 at 15:35

answered Nov 21 '12 at 16:20

D Stanley

139,271
11
154
219

Creative, but need to check if this impacts query performance. It might prevent index usage. – usr Nov 21 '12 at 16:26
1

This worked like a charm for me. At first I had the exact same code as above, however I was forced to choose a default column for the first `OrderBy()` -- I did not know I could put 0 there instead. The 0 will cause the ultimate SQL query to come out with an extra column that has value 0 for every row, then it sorts by that row, achieving the dummy sorting, then it applies all the columns in the chained `ThenBy()'s` that you added. Thanks! – BeemerGuy Feb 08 '17 at 14:59
I'm getting `ArgumentOutOfRangeException` when calling `OrderBy(l => 0)`. [Here](https://pastebin.com/raw/TZQ9g8Es)'s the `StackTrace`. – Shimmy Weitzhandler Mar 07 '18 at 11:07
Apparently this is a [known bug](https://github.com/aspnet/EntityFrameworkCore/issues/10946) in EF Core, please vote / comment. – Shimmy Weitzhandler Mar 07 '18 at 11:09

huysentruitw · Answer 2 · 2017-08-03T13:59:57.303

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 :)

I had the same idea but did not know how to do it, thanks.It worked perfectly. — Mauricio Ferraz, Jul 04 '19 at 14:55

score -1 · Answer 3 · answered Nov 21 '12 at 16:10

-1

May be using all in one Linq query is not the best option from readability point of view. I would use IQueryable to construct your query in memory. Use similar sort of switch statement (but with IQueryable) and then in the end do .ToList (i.e. enumerate) to execute desired query at the server.

answered Nov 21 '12 at 16:10

daehaai

5,155
9
38
57

2

Thanks, but the `query` var here is in fact an IQueryable; the issue is with the OrderBy and ThenBy commands.. It seems as if the ThenBy can only be used when first chained to an OrderBy. – acullen72 Nov 21 '12 at 18:15

Programmatically chain OrderBy/ThenBy using LINQ / Entity Framework

3 Answers3

Linked