OrderBy based on list of fields and Asc / Desc rules

Question

I have the following List with OrderBy parameters:

List<String> fields = new List<String> { "+created", "-approved", "+author" }

This would result in the following Linq query:

IQueryable<Post> posts = _context.posts.AsQueryable();

posts = posts
   .OrderBy(x => x.Created)
   .ThenByDescending(x => x.Approved);
   .ThenBy(x => x.Author.Name);

So basically the rules are:

Use the first item in the OrderBy and the others in ThenBy.
Use descending when the field starts with - and ascending when with +.

My idea would be to have something like:

OrderExpression expression = posts
  .Add(x => x.Created, "created")
  .Add(x => x.Approved, "approved")
  .Add(x => x.Author.Name, "author");

So the expression associates the post properties / child properties to each key in fields. Then it would be applied as follows:

posts = posts.OrderBy(expression, fields);

So the OrderBy extension would go through each item in the OrderExpression and apply the rules (1) and (2) to build the query:

posts = posts
   .OrderBy(x => x.Created)
   .ThenByDescending(x => x.Approved);
   .ThenBy(x => x.Author.Name);

How can this be done?

So the set of possible fields along with the corresponding property selectors are predefined? — Yacoub Massad, Jun 04 '16 at 12:12
Yes, I wanted to keep it simple. So basically the OrderExpressions defines which field is associated with which property — Miguel Moura, Jun 04 '16 at 12:14

Yacoub Massad · Answer 1 · 2016-06-04T13:15:19.787

This following class will help you do it. You can find the explanation of code inline.

public static class MyClass
{
    public static IQueryable<T> Order<T>(
        IQueryable<T> queryable,
        List<string> fields,
        //We pass LambdaExpression because the selector property type can be anything
        Dictionary<string, LambdaExpression> expressions)
    {
        //Start with input queryable
        IQueryable<T> result = queryable;

        //Loop through fields
        for (int i = 0; i < fields.Count; i++)
        {
            bool ascending = fields[i][0] == '+';
            string field = fields[i].Substring(1);

            LambdaExpression expression = expressions[field];

            MethodInfo method = null;

            //Based on sort order and field index, determine which method to invoke
            if (ascending && i == 0)
                method = OrderbyMethod;
            else if (ascending && i > 0)
                method = ThenByMethod;
            else if (!ascending && i == 0)
                method = OrderbyDescendingMethod;
            else
                method = ThenByDescendingMethod;

            //Invoke appropriate method
            result = InvokeQueryableMethod( method, result, expression);
        }

        return result;
    }

    //This method can invoke OrderBy or the other methods without
    //getting as input the expression return value type
    private static IQueryable<T> InvokeQueryableMethod<T>(
        MethodInfo methodinfo,
        IQueryable<T> queryable,
        LambdaExpression expression)
    {
        var generic_order_by =
            methodinfo.MakeGenericMethod(
                typeof(T),
                expression.ReturnType);

        return (IQueryable<T>)generic_order_by.Invoke(
            null,
            new object[] { queryable, expression });
    }

    private static readonly MethodInfo OrderbyMethod;
    private static readonly MethodInfo OrderbyDescendingMethod;

    private static readonly MethodInfo ThenByMethod;
    private static readonly MethodInfo ThenByDescendingMethod;

    //Here we use reflection to get references to the open generic methods for
    //the 4 Queryable methods that we need
    static MyClass()
    {
        OrderbyMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "OrderBy" &&
                        x.GetParameters()
                            .Select(y => y.ParameterType.GetGenericTypeDefinition())
                            .SequenceEqual(new[] { typeof(IQueryable<>), typeof(Expression<>) }));

        OrderbyDescendingMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "OrderByDescending" &&
                        x.GetParameters()
                            .Select(y => y.ParameterType.GetGenericTypeDefinition())
                            .SequenceEqual(new[] { typeof(IQueryable<>), typeof(Expression<>) }));

        ThenByMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "ThenBy" &&
                        x.GetParameters()
                            .Select(y => y.ParameterType.GetGenericTypeDefinition())
                            .SequenceEqual(new[] { typeof(IOrderedQueryable<>), typeof(Expression<>) }));

        ThenByDescendingMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "ThenByDescending" &&
                        x.GetParameters()
                            .Select(y => y.ParameterType.GetGenericTypeDefinition())
                            .SequenceEqual(new[] { typeof(IOrderedQueryable<>), typeof(Expression<>) }));
    }

}

Here is an example usage:

public class Person
{
    public int Age { get; set; }
    public string Name { get; set; }
    public override string ToString()
    {
        return Name + ", " + Age;
    }
}

class Program
{
    static void Main(string[] args)
    {
        List<Person> persons = new List<Person>
        {
            new Person {Name = "yacoub", Age = 30},
            new Person {Name = "yacoub", Age = 32},
            new Person {Name = "adam", Age = 30},
            new Person {Name = "adam", Age = 33},
        };

        var query = MyClass.Order(
            persons.AsQueryable(),
            new List<string> { "+Name", "-Age" },
            new Dictionary<string, LambdaExpression>
            {
                {"Name", (Expression<Func<Person, string>>) (x => x.Name)},
                {"Age", (Expression<Func<Person, int>>) (x => x.Age)}
            });

        var result = query.ToList();
    }
}

I've taken the libery to *borrow* some of your code for a joint answer (yours works better and mine is easier to use). I hope you don't mind. — Manfred Radlwimmer, Jun 04 '16 at 14:47

Manfred Radlwimmer · Answer 2 · 2016-06-04T14:02:59.607

Edit: Changed Code to closely match your syntax

This code sorts on the client, but works with all IEnumerables. If you absolutely need to sort on the database, take a look at Yacoub's static MyClass() to see how he solved this problem.

The example below is based on the information you provided, you might need to adjust it a bit.

public class DemoClass
{
    public DateTime Created { get; set; }
    public bool Approved { get; set; }
    public Person Author { get; set; }
}

public class Person
{
    public string Name { get; set; }
}

Since your example contains author which actually resolves to Author.Name, you need to create some sort of mapping for your keywords (Like you did with your OrderExpression class).

public class OrderExpressions<T>
{
    private readonly Dictionary<string,Func<T,object>> _mappings = 
        new Dictionary<string,Func<T, object>>();

    public OrderExpressions<T> Add(Func<T, object> expression, string keyword)
    {
        _mappings.Add(keyword, expression);
        return this;
    }

    public Func<T, object> this[string keyword]
    {
        get { return _mappings[keyword]; }
    }
}

Which could be used like this:

OrderExpressions<DemoClass> expressions = new OrderExpressions<DemoClass>()
    .Add(x => x.Created, "created")
    .Add(x => x.Approved, "approved")
    .Add(x => x.Author.Name, "author");

You can pass those functions / lambda expressions, directly to Linq and add the next comparison one after another. Start with OrderBy or OrderByDescrending, that will give you your first IOrderedEnumerable and then add all remaining arguments with ThenBy or ThenByDescending.

public static class KeywordSearchExtender
{
    public static IOrderedEnumerable<T> OrderBy<T>(this IEnumerable<T> data, 
        OrderExpressions<T> mapper, params string[] arguments)
    {
        if (arguments.Length == 0)
            throw new ArgumentException(@"You need at least one argument!", "arguments");

        List<SortArgument> sorting = arguments.Select(a => new SortArgument(a)).ToList();

        IOrderedEnumerable<T> result = null;

        for (int i = 0; i < sorting.Count; i++)
        {
            SortArgument sort = sorting[i];
            Func<T, object> lambda = mapper[sort.Keyword];

            if (i == 0)
                result = sorting[i].Ascending ? 
                    data.OrderBy(lambda) : 
                    data.OrderByDescending(lambda);
            else
                result = sorting[i].Ascending ? 
                    result.ThenBy(lambda) : 
                    result.ThenByDescending(lambda);
        }

        return result;
    }
}

public class SortArgument
{
    public SortArgument()
    { }

    public SortArgument(string term)
    {
        if (term.StartsWith("-"))
        {
            Ascending = false;
            Keyword = term.Substring(1);
        }
        else if (term.StartsWith("+"))
        {
            Ascending = true;
            Keyword = term.Substring(1);
        }
        else
        {
            Ascending = true;
            Keyword = term;
        }
    }

    public string Keyword { get; set; }
    public bool Ascending { get; set; }
}

All together it be used like this:

var data = WhateverYouDoToGetYourData();

var expressions = new OrderExpressions<DemoClass>()
            .Add(x => x.Created, "created")
            .Add(x => x.Approved, "approved")
            .Add(x =>x.Author.Name, "author");

var result = data.OrderBy(expressions, "+created", "-approved", "+author");
// OR
var result = data.OrderBy(expressions, fields);

You can find my proof-of-concept on dotNetFiddle.

It seems to me that this solution works with `IEnumerable`, and not `IQueryable`. I think that OP wants a solution that generates a query that can be executed against the database. — Yacoub Massad, Jun 04 '16 at 13:17
`IQueryable` inherits from `IEnumerable` so it will work with both. If not, it's a simple change to the extension method. — Manfred Radlwimmer, Jun 04 '16 at 13:21
You would need to use `Expression>` instead of `Func`. Also note that you will might face problems if you use `Expression>` instead of `Expression>`. — Yacoub Massad, Jun 04 '16 at 13:23
@YacoubMassad Good idea, If it's an `Expression<>` it shoud convertible to SQL - I'll do that. Hard to get rid of `TKey` — Manfred Radlwimmer, Jun 04 '16 at 13:49
I am interested to see if it now works against a database. I remember once that using `Expression>` instead of `Expression>` didn't work for me. — Yacoub Massad, Jun 04 '16 at 13:56
Unfortunately it doesn't, Linq can't cast Sql datatypes to `object` — Manfred Radlwimmer, Jun 04 '16 at 13:58
This is why I have the `InvokeQueryableMethod` method in my answer and why I used reflection to call the `OrderBy` and other methods instead of calling them directly. — Yacoub Massad, Jun 04 '16 at 14:00

score 1 · Accepted Answer · edited May 23 '17 at 11:58

This answer is the joint effort of @YacoubMassad and me. Take a look at the separate answers for details. The following code works perfectly and even translates to SQL without issues (I checked the query with the answer to this question on a 2008 R2), so all the sorting is done on the server (or wherever you data is, it works with simple lists too of course).

Example usage:

OrderExpression<Post> expression = new OrderExpression<Post>()
    .Add(x => x.Created, "created")
    .Add(x => x.Approved, "approved")
    .Add(x => x.Author.Name, "author");

IQueryable<Post> posts = _context.posts.AsQueryable();

posts = posts.OrderBy(expression, "+created", "-approved", "+author");
// OR
posts = posts.OrderBy(expression, new string[]{"+created", "-approved", "+author"});
// OR
posts = posts.OrderBy(expression, fields.ToArray[]);

And of course a live demo on dotNetFiddle

Code:

public class OrderExpressions<T>
{
    private readonly Dictionary<string, LambdaExpression> _mappings = 
        new Dictionary<string, LambdaExpression>();

    public OrderExpressions<T> Add<TKey>(Expression<Func<T, TKey>> expression, string keyword)
    {
        _mappings.Add(keyword, expression);
        return this;
    }

    public LambdaExpression this[string keyword]
    {
        get { return _mappings[keyword]; }
    }
}

public static class KeywordSearchExtender
{
    private static readonly MethodInfo OrderbyMethod;
    private static readonly MethodInfo OrderbyDescendingMethod;

    private static readonly MethodInfo ThenByMethod;
    private static readonly MethodInfo ThenByDescendingMethod;

    //Here we use reflection to get references to the open generic methods for
    //the 4 Queryable methods that we need
    static KeywordSearchExtender()
    {
        OrderbyMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "OrderBy" &&
                x.GetParameters()
                    .Select(y => y.ParameterType.GetGenericTypeDefinition())
                    .SequenceEqual(new[] { typeof(IQueryable<>), typeof(Expression<>) }));

        OrderbyDescendingMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "OrderByDescending" &&
                x.GetParameters()
                    .Select(y => y.ParameterType.GetGenericTypeDefinition())
                    .SequenceEqual(new[] { typeof(IQueryable<>), typeof(Expression<>) }));

        ThenByMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "ThenBy" &&
                x.GetParameters()
                    .Select(y => y.ParameterType.GetGenericTypeDefinition())
                    .SequenceEqual(new[] { typeof(IOrderedQueryable<>), typeof(Expression<>) }));

        ThenByDescendingMethod = typeof(Queryable)
            .GetMethods()
            .First(x => x.Name == "ThenByDescending" &&
                x.GetParameters()
                    .Select(y => y.ParameterType.GetGenericTypeDefinition())
                    .SequenceEqual(new[] { typeof(IOrderedQueryable<>), typeof(Expression<>) }));
    }

    //This method can invoke OrderBy or the other methods without
    //getting as input the expression return value type
    private static IQueryable<T> InvokeQueryableMethod<T>(
        MethodInfo methodinfo,
        IQueryable<T> queryable,
        LambdaExpression expression)
    {
        var generic_order_by =
            methodinfo.MakeGenericMethod(
                typeof(T),
                expression.ReturnType);

        return (IQueryable<T>)generic_order_by.Invoke(
            null,
            new object[] { queryable, expression });
    }

    public static IQueryable<T> OrderBy<T>(this IQueryable<T> data, 
        OrderExpressions<T> mapper, params string[] arguments)
    {
        if (arguments.Length == 0)
            throw new ArgumentException(@"You need at least one argument!", "arguments");

        List<SortArgument> sorting = arguments.Select(a => new SortArgument(a)).ToList();

        IQueryable<T> result = null;

        for (int i = 0; i < sorting.Count; i++)
        {
            SortArgument sort = sorting[i];
            LambdaExpression lambda = mapper[sort.Keyword];

            if (i == 0)
                result = InvokeQueryableMethod(sort.Ascending ? 
                    OrderbyMethod : OrderbyDescendingMethod, data, lambda);
            else
                result = InvokeQueryableMethod(sort.Ascending ? 
                    ThenByMethod : ThenByDescendingMethod, result, lambda);
        }

        return result;
    }
}

public class SortArgument
{
    public SortArgument()
    { }

    public SortArgument(string term)
    {
        if (term.StartsWith("-"))
        {
            Ascending = false;
            Keyword = term.Substring(1);
        }
        else if (term.StartsWith("+"))
        {
            Ascending = true;
            Keyword = term.Substring(1);
        }
        else
        {
            Ascending = true;
            Keyword = term;
        }
    }

    public string Keyword { get; set; }
    public bool Ascending { get; set; }
}

Thank you very much. This works great. I just marked this one as the answer. — Miguel Moura, Jun 05 '16 at 14:38

OrderBy based on list of fields and Asc / Desc rules

3 Answers3

Linked

Related