OrderBy 基于字段列表和 Asc / Desc 规则

Question

我有以下带有OrderBy参数的List：

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

这将导致以下 Linq 查询：

IQueryable<Post> posts = _context.posts.AsQueryable();
posts = posts
   .OrderBy(x => x.Created)
   .ThenByDescending(x => x.Approved);
   .ThenBy(x => x.Author.Name);

所以基本上规则是：

1. 使用OrderBy中的第一项，使用ThenBy中的其他项。
2. 当字段以 - 开头时使用降序，当字段以 + 开头时使用升序。

我的想法是有这样的东西：

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

因此，表达式将 post 属性/子属性与字段中的每个键相关联。然后，它将按如下方式应用：

posts = posts.OrderBy(expression, fields);

因此，OrderBy扩展将遍历 OrderExpression 中的每个项目，并应用规则 （1） 和 （2） 来构建查询：

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

如何做到这一点？

Answer 1

下面的课程将帮助您做到这一点。您可以找到内联代码的说明。

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

下面是一个用法示例：

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

Answer 2

这个答案是我和@YacoubMassad共同努力的结果。有关详细信息，请查看单独的答案。以下代码运行良好，甚至可以毫无问题地转换为 SQL（我在 2008 R2 上检查了查询和这个问题的答案），因此所有排序都是在服务器上完成的（或者无论您的数据在哪里，它当然也适用于简单的列表）。

用法示例：

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

当然还有dotNetFiddle上的现场演示

法典：

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

Answer 3

编辑：更改了代码以与您的语法紧密匹配

此代码在客户端上排序，但适用于所有IEnumerables。如果你绝对需要对数据库进行排序，看看Yacoub的static MyClass()，看看他是如何解决这个问题的。

下面的示例基于您提供的信息，您可能需要对其进行一些调整。

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

由于您的示例包含实际上解析为 Author.Name 的author，因此您需要为关键字创建某种映射（就像您对OrderExpression类所做的那样）。

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

可以这样使用：

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

您可以将这些函数/lambda 表达式直接传递给 Linq，并逐个添加下一个比较。从 OrderBy 或 OrderByDescrending 开始，这将为您提供第一个IOrderedEnumerable，然后添加所有剩余的参数，并带有 ThenBy 或 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; }
}

总而言之，它是这样使用的：

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

你可以在dotNetFiddle上找到我的概念验证。