Sort IQueryable by specific List value order

Question

Is it possible to sort IQueryable by indexes of other list, without cast to IEnumerable. Entity Framework and Linq can't translate it to SQL query. I also tried Dictionary<int, Enum> and function which returns int order.

And I don't want enum values.

Example:

public enum Letter
{
    A = 0,
    B = 1,
    C = 2
}

public class MyClass
{
    public Letter LetterEnum { get; set; }
    public DateTime Date { get; set; }
}

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
{
    List<Letter> preferredOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };
    return data.OrderBy(x=> preferredOrder.IndexOf(x.LetterEnum)).ThenBy(x=> x.Date);
}

[LINQ to Entities does not recognize the method 'Int32 IndexOf](https://stackoverflow.com/questions/23211725/linq-to-entities-does-not-recognize-the-method-int32-indexofsystem-string-sys) — Tim Schmelter, Jun 07 '23 at 14:24

Salman A · Accepted Answer · 2023-06-08T09:39:15.423

4

If it were a SQL question I would suggest using case expression. In linq you could write:

return data
           .OrderBy(x => x.LetterEnum == Letter.B ? 1 :
                         x.LetterEnum == Letter.C ? 2 :
                         x.LetterEnum == Letter.A ? 3 : (int?) null)
           .ThenBy(x => x.Date);

Generated SQL:

ORDER BY CASE
    WHEN CAST([t].[LetterEnum] AS int) = 1 THEN 1
    WHEN CAST([t].[LetterEnum] AS int) = 2 THEN 2
    WHEN CAST([t].[LetterEnum] AS int) = 0 THEN 3
    ELSE NULL
END, [t].[Date]

edited Jun 08 '23 at 09:39

answered Jun 07 '23 at 16:59

Salman A

262,204
82
430
521

Well, probably better solution than mine, but for dynamic realisation needs a little bit knowledge in Expression Tree generation. – Svyatoslav Danyliv Jun 07 '23 at 20:54
I did not test what query it generates TBH, but if it creates `case when ...` then I'm OK. Other, better solution (for longer lists) is to join with value-weight pairs. – Salman A Jun 07 '23 at 21:00
1

It is ok, Join to local collection is not supported by EF. – Svyatoslav Danyliv Jun 07 '23 at 21:15

score 2 · Answer 2 · edited Jul 09 '23 at 12:28

You can build sorting query dynamically:

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
{
    var preferredOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };

    IOrderedQueryable<MyClass>? sorted = null;

    foreach (var orderItem in preferredOrder)
    {
        if (sorted == null)
        {
            sorted = data.OrderBy(x => x.LetterEnum == orderItem ? 0 : 1);
        }
        else
        {
            sorted = sorted.ThenBy(x => x.LetterEnum == orderItem ? 0 : 1);
        }
    }

    if (sorted == null)
    {
        sorted = data.OrderBy(x => x.Date);
    }
    else
    {
        sorted = sorted.ThenBy(x => x.Date);
    }

    return sorted;
}

Schematically it will generate the following:

    return data
        .OrderBy(x => x.LetterEnum == Letter.B ? 0 : 1)
        .ThenBy(x => x.LetterEnum == Letter.C ? 0 : 1)
        .ThenBy(x => x.LetterEnum == Letter.A ? 0 : 1)
        .ThenBy(x => x.Date);

score 1 · Answer 3 · edited Jul 09 '23 at 12:27

Dynamic order

Based on the list you specified, they will create a sort

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
{
    List<Letter> preferredOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };

    for (int i = 0; i <= preferredOrder.Count; ++i)
    {
        var value = i == preferredOrder.Count ? 0 : preferredOrder[i];

        if (data is IOrderedQueryable<MyClass> orderedQuery)
        {
            if (i == preferredOrder.Count)
                data = orderedQuery.ThenBy(d => d.Date);
            else
                data = orderedQuery.ThenBy(d => d.LetterEnum == value);
        }
        else
        {
            data = data.OrderBy(d => d.LetterEnum == value);
        }
    }

    return data;
}

Sort IQueryable by specific List value order

3 Answers3

Linked