根据字段列表和升序/降序规则排序OrderBy

3

我有以下带有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");

因此,该表达式将帖子属性/子属性与fields中的每个键相关联。然后将按以下方式应用:

posts = posts.OrderBy(expression, fields);

所以OrderBy扩展会遍历OrderExpression中的每个项,并应用规则(1)和(2)来构建查询:
posts = posts
   .OrderBy(x => x.Created)
   .ThenByDescending(x => x.Approved);
   .ThenBy(x => x.Author.Name);

这怎么做呢?

那么可能的字段集合以及相应的属性选择器是预定义的吗? - Yacoub Massad
是的,我想保持简单。因此,OrderExpressions基本上定义了哪个字段与哪个属性相关联。 - Miguel Moura
3个回答

2
这个类可以帮助你实现这一点。你可以在代码中找到解释。
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();
    }
}

我已经冒昧地“借用”了你的一些代码来合并答案(你的代码更好,而我的更易于使用)。希望你不介意。 - Manfred Radlwimmer
没问题 @ManfredRadlwimmer - Yacoub Massad

1

编辑:将代码更改为与您的语法更接近

此代码在客户端上进行排序,但适用于所有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.Nameauthor,因此您需要为关键字创建某种映射(就像您的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,并在一起添加下一个比较。从OrderByOrderByDescrending开始,这将为您提供第一个IOrderedEnumerable,然后使用ThenByThenByDescending添加所有剩余的参数。
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上找到我的概念证明


在我看来,这个解决方案适用于 IEnumerable<T> 而不是 IQueryable<T>。我认为 OP 希望有一个可以对数据库执行查询的解决方案。 - Yacoub Massad
IQueryable 继承自 IEnumerable,因此它可以与两者一起使用。如果不能,那么修改扩展方法就可以了。 - Manfred Radlwimmer
你需要使用 Expression<Func<T, TKey>>,而不是 Func<T, TKey>。另外请注意,如果你使用 Expression<Func<T, object>> 而不是 Expression<Func<T, TKey>>,可能会遇到问题。 - Yacoub Massad
@YacoubMassad 好主意,如果它是一个 Expression<>,那么它应该可以转换为 SQL - 我会这样做的。很难摆脱 TKey - Manfred Radlwimmer
不幸的是,Linq不能将Sql数据类型转换为“object”。 - Manfred Radlwimmer
显示剩余2条评论

1

这个答案是我和@YacoubMassad共同努力的结果。请查看单独的答案以获取详细信息。以下代码完美地工作,甚至可以无问题地转换为SQL(我在2008 R2上使用this question的答案检查了查询),因此所有排序都在服务器上完成(或者您的数据所在的任何地方,当然也适用于简单列表)。

示例用法:

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

非常感谢。这个方案很有效。我刚刚将其标记为答案。 - Miguel Moura

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接