OrderBy基于字段列表和Asc / Desc规则 [英] OrderBy based on list of fields and Asc / Desc rules

问题描述

我有以下列表与 OrderBy 参数：

 列表与LT;字符串> 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）; 
  

所以基本上规则是：

<

使用 OrderBy 中的第一项，而 ThenBy 中的其他项目。

当字段以 - 开始时使用降序，当使用 + 时升序。

我的想法将是：

  OrderExpression expression = posts 
 .Add（x => x.Created，created）
 .Add（x => x.Approved，approved）
。 Add（x => x.Author.Name，author）; 
  

所以表达式将post属性/子属性与字段中的每个键相关联。那么它将被应用如下：

  posts = posts.OrderBy（表达式，字段）; 
  

所以 OrderBy 项目在OrderExpression中并应用规则（1）和（2）构建查询：

  posts = posts 
 .OrderBy（x => x.Created）
 .ThenByDescending（x => x.Approved）; 
 .ThenBy（x => x.Author.Name）; 
  

如何做？

解决方案

这个答案是 @YacoubMassad 和我共同努力的结果。看看单独的答案了解详情。以下代码完美工作，甚至可以翻译成SQL，而不会出现问题（我已经查询了这个问题的答案问题），所以所有的排序都是在服务器上完成的（或者数据在哪里，当然也可以使用简单的列表）。

示例用法

  OrderExpression< Post> 
 .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>添加< TKey>（表达式< Func< T，TKey>>>表达式，字符串关键字）
 {
 _mappings.Add（keyword，expression）; 
返回这个; 
} 
 
 public LambdaExpression this [string keyword] 
 {
 get {return _mappings [keyword]; b 
 
 $ b public static class KeywordSearchExtender 
 {
 private static readonly MethodInfo OrderbyMethod; 
 private static readonly MethodInfo OrderbyDescendingMethod; 
 
 private static readonly MethodInfo ThenByMethod; 
 private static readonly MethodInfo ThenByDescendingMethod; 
 
 //这里我们使用反射来获取对
的开放通用方法的引用//我们需要的4个Queryable方法
 static KeywordSearchExtender（）
 {
 OrderbyMethod = typeof（Queryable）
 .GetMethods（）
 .First（x => x.Name ==OrderBy&& 
 x.GetParameters（）
。选择（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<>）} ））; 
} 
 
 //此方法可以调用OrderBy或其他方法，而不使用
 //获取输入表达式返回值类型
 private static IQueryable< T> InvokeQueryableMethod< T>（
 MethodInfo methodinfo，
 IQueryable< T>可查询，
 LambdaExpression表达式）
 {
 var generic_order_by = 
 methodinfo.MakeGenericMethod b $ b typeof（T），
 expression.ReturnType）; 
 
 return（IQueryable< T>）generic_order_by.Invoke（
 null，
 new object [] {queryable，expression}）; 
} 
 
 public static IQueryable< T> OrderBy< T>（这个IQueryable< T>数据，
 OrderExpressions&T; mapper，params string []参数）
 {
 if（arguments.Length == 0）
抛出新的ArgumentException（@你至少需要一个参数！，arguments）; 
 
列表< SortArgument> sorted = arguments.Select（a => new SortArgument（a））。ToList（）; 
 
 IQueryable< T> result = null; （int i = 0; i< sort.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）; 
} 
 
返回结果; 
} 
} 
 
 public class SortArgument 
 {
 public SortArgument（）
 {} 
 
 public SortArgument （string term）
 {
 if（term.StartsWith（ - ））
 {
 Ascending = false; 
关键字= term.Substring（1）; 
} 
 else if（term.StartsWith（+））
 {
 Ascending = true; 
关键字= term.Substring（1）; 
} 
 else 
 {
 Ascending = true; 
关键字=术语; 
} 
} 
 
 public string关键字{get;组; } 
 public bool Ascending {get;组; } 
} 
  

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:

1. Use the first item in the OrderBy and the others in ThenBy.
2. 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?

解决方案

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

这篇关于OrderBy基于字段列表和Asc / Desc规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！