Order column by a separate `List` variable

Question

I need to sort the results of an entity framework query by the index of elements in another list.

I tried the suggestion found elsewhere, like

.ThenBy(m=>list.IndexOf(m.Term))

but I get an HTTP 500 error, so I'm wondering if I'm missing something. When debugging, I get this inner exception.

LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String)' method, and this method cannot be translated into a store expression.

In particular, I'm thinking of this scenario.

private IEnumerable<MiaLog1A> PopulateQuery(string selectedCampus)
{
    var list = new List<string> {"No Longer Alphabetical","Fall","Mid","Spring"};
    return _db.MiaLog1A.Where(m => m.Campus == selectedCampus)
            .OrderBy(m => m.StudentName)
            .ThenBy(m=>m.Term)  
                    /* I would like to sort "Term" by the 
                     * order of "list".
                     */
        .AsEnumerable();
}

Is there a way I could sort in this manner, or am I restricted to ordering by a dictionary or creating a join?

The answer to [this question](http://stackoverflow.com/questions/15275269/sort-a-list-from-another-list-ids) might help. — sgbj, Aug 31 '16 at 20:08
@sbat nope, that question is sorting in memory, OP is using EF, which is trying to rewrite the queryable as sql statements. The 500 error demonstrates this. — , Aug 31 '16 at 20:46
@Will I know it's an old question but posted an answer that is using an IQueryable. — Filip Cordas, May 26 '17 at 21:46

keydon · Accepted Answer · 2016-08-31T20:19:09.023

3

this should do the trick:

private IEnumerable<MiaLog1A> PopulateQuery(string selectedCampus)
{
    var list = new List<string> {"Fall","Mid","Spring"};
    return _db.MiaLog1A.Where(m => m.Campus == selectedCampus)
        .AsEnumerable()
        .OrderBy(m => m.StudentName)
        .ThenBy(m=> list.IndexOf(m.Term));
}

edited Aug 31 '16 at 20:19

answered Aug 31 '16 at 20:07

keydon

729
6
8

After debugging, I'm getting this inner exception: "LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String)' method, and this method cannot be translated into a store expression." – Aug 31 '16 at 20:12
ah right, its that SQL-Linq you are using. Dont know how it works there. But should work with the Enumberable-LINQ. So maybe consider sorting after `.AsEnumerable()` – keydon Aug 31 '16 at 20:15
edited my answer to sort after the query got built and processed. – keydon Aug 31 '16 at 20:21
That's the key. I keep forgetting I have to some operations _after_ the `.AsEnumerable()`... – Aug 31 '16 at 20:23
@Rubix_Revenge it's important to understand the magic behind EF and queryables. There's plenty of info out there on the subject, so if you aren't familiar with how EF generates sql from your linq statements, take an hour and read up on it. You'll thank yourself. – Aug 31 '16 at 20:47

score 2 · Answer 2 · edited May 30 '17 at 12:59

Here is a mad way to do it but it doesn't fall back to IEnumerable

var db = new BloggingContext();

var list = (new List<string> { "FR", "EN" }).
    Select((s,i)=> $"select '{s}' as {nameof(OrderIndex.Name)},{i} as {nameof(OrderIndex.Id)}");

var order = db.Set<OrderIndex>().FromSql(String.Join(" union ",list));

var orderedItems = from post in db.Posts
                   join ln in order on post.Lang equals ln.Name into lnPost
                   from od in lnPost.DefaultIfEmpty()
                   orderby od.Id
                   select post;
var data = orderedItems.ToList();

You can find the definition of the BloggingContext here I just added a Lang field for a language code. This will work in SQLite, SqlServer and MySQL for oracle you need to add from dual Due to how terrible EF core is you will need to go true the same procedure for OrderIndex as you would with an SQL view. In EF 6 there is a much nicer way of doing this with SqlQuery and you don't need to do the registration like with EF core. The created query is

SELECT "post"."PostId", "post"."BlogId", "post"."Content", "post"."Date", "post"."Lang", "post"."Title", "ln"."Id", "ln"."Name"
    FROM "Posts" AS "post"
    LEFT JOIN (
        select 'FR' as Name,0 as Id union select 'EN' as Name,1 as Id
    ) AS "ln" ON "post"."Lang" = "ln"."Name"
    ORDER BY "ln"."Id", "post"."Lang"

EDIT: Just remembered a different way to do it it's not as mad but might be better.

var lang = new List<string> { "FR", "EN" };    
var orderedItems = from post in db.Posts
                   orderby (lang[0] == post.Lang) ? 
                   0 :((lang[1] == post.Lang) ? 1 : 2)
                   select post;

var param = Expression.Parameter(typeof(Post));

var order = lang.Select((s, i) => new { s, i })
    .Aggregate((Expression)Expression.Constant(lang.Count), (agg, i) =>
        Expression.Condition(
            Expression.Equal(Expression.Property(param,nameof(Post.Lang)),
            Expression.Constant(i.s)),
            Expression.Constant(i.i),
            agg));
var exp = Expression.Lambda<Func<Post, int>>(order, param);
var data = db.Posts.OrderBy(exp).ToList();

And the SQL

SELECT "p"."PostId", "p"."BlogId", "p"."Content", "p"."Date", "p"."Lang", "p"."Title"
    FROM "Posts" AS "p"
    ORDER BY CASE
        WHEN "p"."Lang" = 'EN'
        THEN 1 ELSE CASE
            WHEN "p"."Lang" = 'FR'
            THEN 0 ELSE 2
        END
    END

I still think the mad way is useful for something I just don't know what.

Order column by a separate `List` variable

2 Answers2

Linked

Related