How to do OrderBy on top of Queryable from other given list of Guids

Question

I have search service that will give me the list of Guids of my entity with the correct order. For example I search service gave me this result:

// Assuming you have a list of Guids named 'guidList'
List<Guid> guidList = new List<Guid>
{
    Guid.NewGuid(),
    Guid.NewGuid(),
    Guid.NewGuid()
};

Now, I need to do a query to the database to get those entities:

using (var context = new YourDbContext())
{
    var students = context.Students
        .Where(e => guidList.Contains(e.Guid));
}

I want to get my entities in the same order as my guidList. Doing AsEnumerable will not work here because I will add another query at the end and it may affect to performance

Does this answer your question? [Sort IQueryable by specific List value order](https://stackoverflow.com/questions/76424326/sort-iqueryable-by-specific-list-value-order) — Svyatoslav Danyliv, Jul 18 '23 at 09:56
You're using EF Core to generate SQL statements. You *can't* specify an order like this in SQL. You'd have to either generate a temporary table with the GUIDs and an incrementing number, or pass a table parameter containing that data. Or emulate the complex `ORDER BY` shown in the duplicate. It's a *lot* cheaper and easier to retrieve the data and re-sort them on the client — Panagiotis Kanavos, Jul 18 '23 at 10:21
`Doing AsEnumerable will not work here because I will add another query at the end and it may affect to performance` what query? Post the full code. Applying another query will *change* the order of results. There's no implied order in a query without an ORDER BY clause in the outermost query. The fix is the same anyway - load the results, then order them in memory. — Panagiotis Kanavos, Jul 18 '23 at 10:27
@PanagiotisKanavos, have you looked at the answer? It contains solution for `IQueryable`. — Svyatoslav Danyliv, Jul 18 '23 at 10:31
@SvyatoslavDanyliv expensive tricks that emulate Salman's answer, yes. While interesting from an intellectual perspective, they offer no benefit and could cause the optimizer to come up with a bad execution plan — Panagiotis Kanavos, Jul 18 '23 at 10:33
@SvyatoslavDanyliv besides the OP says this is just an *inner* query. Any order applied will probably be altered by extra operators — Panagiotis Kanavos, Jul 18 '23 at 10:35
@PanagiotisKanavos, agree with bad execution plan. OP should decide what to do. If temporary tables are needed, will show how to do that with EF Core extension. — Svyatoslav Danyliv, Jul 18 '23 at 10:43
@SvyatoslavDanyliv It seems link above works, but only thing how to make it 'dynamically'. For example, I will have list of guids with 10, 20, ... count. How to build this kind of query? — Dilshod K, Jul 18 '23 at 10:51
Well, I'll prepare such function. If this question will be closed, open new one with requirement that you need dynamic generation. — Svyatoslav Danyliv, Jul 18 '23 at 10:59
@SvyatoslavDanyliv Could you post it here? It will be related — Dilshod K, Jul 18 '23 at 11:09

score 5 · Accepted Answer · answered Jul 18 '23 at 11:31

This is generic function which dynamically generates Expression Tree in the following manner:

query.OrderByDescending(e =>
      e.SomeProp == itemList[0] ? 0
    : e.SomeProp == itemList[1] ? 1
    : e.SomeProp == itemList[2] ? 2
    ... itemList.Count);

Usage in your case is simple:

context.Students
    .Where(e => guidList.Contains(e.Guid))
    .OrderByItems(e => e.Guid, guidList);

And implemntation:

public static class QueryableExtensions
{
    public static IQueryable<T> OrderByItems<T, TItem>(this IQueryable<T> query, Expression<Func<T, TItem>> prop, IEnumerable<TItem> items)
    {
        var conditions = items
            .Select(item => Expression.Equal(prop.Body, Expression.Constant(item, typeof(TItem))))
            .ToList();

        // nothing to sort
        if (conditions.Count == 0)
            return query;

        Expression orderExpr = Expression.Constant(conditions.Count);

        for (var i = conditions.Count - 1; i >= 0; i--)
        {
            var condition = conditions[i];
            orderExpr = Expression.Condition(condition, Expression.Constant(i), orderExpr);
        }

        var entityParam = prop.Parameters[0];
        var orderLambda = Expression.Lambda<Func<T, int>>(orderExpr, entityParam);

        return query.OrderByDescending(orderLambda);
    }
}

score 1 · Answer 2 · answered Jul 18 '23 at 11:24

1

Another approach would be to sort the values on client side:

var guidList = ...;
var students = context.Students...ToList();

var ordered = guidList.Join(students, id => id, student => student.Guid, (id, student) => student);

answered Jul 18 '23 at 11:24

Oliver

43,366
8
94
151

Krishna Varma · Answer 3 · 2023-07-18T10:58:54.040

0

Did you try IndexOf

using (var context = new YourDbContext())
{
    var students = context.Students
        .Where(e => guidList.Contains(e.Guid))
        .AsEnumerable()
        .OrderBy(e => guidList.IndexOf(e.Guid))
        .ToList();
}

edited Jul 18 '23 at 10:58

answered Jul 18 '23 at 10:01

Krishna Varma

4,238
2
10
25

I tried this, but got an error: InvalidOperationException: The LINQ expression 'DbSet() .Where(t => __guids_0.Contains(((Student)t).Guid)) .OrderBy(t => __guids_0.IndexOf(((Student)t).Guid))' could not be translated – Dilshod K Jul 18 '23 at 10:07
EF Core generates SQL. There's no equivalent of `List.IndexOf` in SQL. On the other hand, this `OrderBy` could be used *after* `ToList` to reorder the items in memory. – Panagiotis Kanavos Jul 18 '23 at 10:24
@DilshodK modified my post – Krishna Varma Jul 18 '23 at 10:59
@KrishnaVarma, I see, but as I mentioned on my question, Doing AsEnumerable will not work in my case – Dilshod K Jul 18 '23 at 11:10

How to do OrderBy on top of Queryable from other given list of Guids

3 Answers3