Dynamic Order By in Linq

Question

I have an app that accesses database and has to order results by different fields depending on the input.

Here is my function for sorting:

IQueryable<Entity> GetSortedData(IQueryable<Entity> result, String orderby, bool desc)
{
   switch (orderby.ToLower())
   {
     case "id":
       result = result.OrderBy(c => c.Id);
       break;

     case "code":
       result = result.OrderBy(c => c.Code);
       break;

     case "active":
       result = result.OrderBy(c => c.Active);
       break;

     default:
       result = result.OrderBy(c => c.Name);
       break;
   }

   if (pageData.SortDesc)
   {
     var res = result.ToList();
     res.Reverse();
     return res.AsQueryable();
   }

   return result;      
}

There are some problems with this code that I don't like:

1. Too much repetitive code. If it was "pure SQL" query, it would look like

   SELECT * FROM data_table ORDER BY CASE @OrderBy WHEN 'id' THEN id WHEN 'code' THEN code
   WHEN 'active' THEN active ELSE name
   END ;

2. Conversion to list and back for reversing. I can not change return type and I definitely do not want to write even more useless code (essentially doubling switch case with OrderByDescending).

Can anyone suggest ways of making this function better-looking, preferably still using LINQ?

Answer 1

Well, you definitely want to use OrderByDescending instead of reversing. It's not going to be quite as brief as the SQL, but you could at least use:

IQueryable<Entity> GetSortedData(IQueryable<Entity> result, String orderby, bool desc)
{
   switch (orderby.ToLowerInvariant())
   {
     case "id":
         return desc ? result.OrderByDescending(c => c.Id) : result.OrderBy(c => c.Id);
     case "code":
         return desc ? result.OrderByDescending(c => c.Code) : result.OrderBy(c => c.Code);
     case "active":
         return desc ? result.OrderByDescending(c => c.Active) : result.OrderBy(c => c.Active);
     default:
         return desc ? result.OrderByDescending(c => c.Name) : result.OrderBy(c => c.Name);
   }
}

You could remove that repetition with your own extension method:

public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    bool descending) =>
    descending ? source.OrderByDescending(keySelector) : source.OrderBy(keySelector);

Then write:

IQueryable<Entity> GetSortedData(IQueryable<Entity> result, String orderby, bool desc)
{
   switch (orderby.ToLowerInvariant())
   {
     case "id": return result.OrderBy(c => c.Id, desc);
     case "code": return result.OrderBy(c => c.Code, desc);
     case "active": return result.OrderBy(c => c.Active, desc);
     default: return result.OrderBy(c => c.Name, desc);
   }
}

Answer 2

// this will work with any class
IQueryable<Entity> GetSortedData(
        IQueryable<Entity> result, String orderby, bool desc)
{
    return result.OrderBy(orderby, desc);
}


// custom order by extension method
// which will work with any class
public static class QueryableHelper
{

   public static IQueryable<TModel> OrderBy<TModel>
     (this IQueryable<TModel> q, string name, bool desc)
   {
      Type entityType = typeof(TModel);
      PropertyInfo p = entityType.GetProperty(name);
      MethodInfo m = typeof(QueryableHelper)
       .GetMethod("OrderByProperty")
       .MakeGenericMethod(entityType, p.PropertyType);
      return(IQueryable<TModel>) m.Invoke(null, new object[] { 
         q, p , desc });
   }


    public static IQueryable<TModel> OrderByProperty<TModel, TRet>
       (IQueryable<TModel> q, PropertyInfo p, bool desc)
    {
        ParameterExpression pe = Expression.Parameter(typeof(TModel));
        Expression se = Expression.Convert(Expression.Property(pe, p), typeof(object));
        var exp = Expression.Lambda<Func<TModel, TRet>>(se, pe);
        return desc ? q.OrderByDescending(exp) : q.OrderBy(exp);
    }

}