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

}

Answer 3

You can use this extension of IQueryable:

using System.Linq.Dynamic;

   public static class IQuerableExtensions
    {
        public static IQueryable<T> ApplySort<T>(this IQueryable<T> source, string sort)
        {
            if(source == null)
            {
                throw new ArgumentNullException("source");
            }

            if(sort == null)
            {
                return source;
            }

            var lsSort = sort.Split(',');

            // run through the sorting options and create a sort expression string from them
            string completeSortExpression = "";
            foreach (var sortOption in lsSort)
            {
                // if the sort option starts with "-", we order descending, otherwise ascending
                if(sortOption.StartsWith("-"))
                {
                    completeSortExpression = completeSortExpression + sortOption.Remove(0, 1) + " descending,";
                }
                else
                {
                    completeSortExpression = completeSortExpression + sortOption + ",";
                }
            }

            if (!string.IsNullOrWhiteSpace(completeSortExpression))
            {
                source = source.OrderBy(completeSortExpression.Remove(completeSortExpression.Count() - 1));
            }

            return source;
        }
    }

In order to use it, just type:

yourList.AsQueryable().ApplySort("thefieldName")

If you put a - before the fieldName, then the order by will be descending.

yourList.AsQueryable().ApplySort("-thefieldName")

Answer 4

Although I prefer the strongly-typed method that @Jon Skeet provided, if you still want to do it stringly-typed, you could probably do something like this:

public static IQueryable<Entity> Sort(this IQueryable<Entity> collection, string sortBy, bool desc = false)
{
    return collection.OrderBy(sortBy + (desc ? " descending" : ""));
}

This requires the dynamic LINQ library, which you can install via

install-package System.Linq.Dynamic

Answer 5

I am not sure, what is the source of the String orderby, but if that is something you specify in the code, then it might be better to just specify a delegate instead of a string. This way, your code will look something like below

IQueryable<Entity> GetSortedData(IQueryable<Entity> result, Func orderSelector, bool desc) {
    return result.OrderBy(orderSelector, desc);
}

And just like Jon suggested, you can use the extension to simplify the ascending vs descending order.

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

So, if your call looked like this

GetSortedData(result, "Id", true)

Now it should look like this

GetSortedData(result, (e) => e.Id, true)

PS: Sometimes, the field on which to sort is picked up from a UI control. In such cases, you can use their .Tag or similar property to store the delegate along side.