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

Answer 1

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);
    }
}

Answer 2

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

Answer 3

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();
}