与实体框架和LINQ的日期进行动态顺序[英] Dynamic Order By On Date With Entity Framework and Linq

本文是小编为大家收集整理的关于与实体框架和LINQ的日期进行动态顺序的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我还有另一个问题在这里回答了null值在订单上的最后一个问题.

.

日期列进行相同的事情.

  1. 所有最终日期的所有项目都在当前日期和顶部,以最新的即将到来的事件进行排序
  2. 随后使用结束日期的所有过去事件,并与当前日期进行比较,并与最近的结束日期和下降.我目前在纯SQL中做类似的事情.

            (CASE 
                WHEN ev.EndDate >= GETDATE() THEN 1
                ELSE 2
            END) ASC,
            (CASE
                WHEN ev.EndDate >= GETDATE() THEN ev.EndDate
                ELSE ev.StartDate
            END) ASC,
    

示例:当前日期3/24/2017

enddate

3/25/2017

4/15/2017

7/29/2017

3/23/2017

2/22/2016

当前代码

public static class OrderByHelper
{
    public static IOrderedQueryable<T> ThenBy<T>(this IEnumerable<T> source, string orderBy)
    {
        return source.AsQueryable().ThenBy(orderBy);
    }

    public static IOrderedQueryable<T> ThenBy<T>(this IQueryable<T> source, string orderBy)
    {
        return OrderBy(source, orderBy, false);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IEnumerable<T> source, string orderBy)
    {
        return source.AsQueryable().OrderBy(orderBy);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string orderBy)
    {
        return OrderBy(source, orderBy, true);
    }

    private static IOrderedQueryable<T> OrderBy<T>(IQueryable<T> source, string orderBy, bool initial)
    {
        if (string.IsNullOrWhiteSpace(orderBy))
            orderBy = "ID DESC";
        var parameter = Expression.Parameter(typeof(T), "x");
        var expression = source.Expression;
        foreach (var item in ParseOrderBy(orderBy, initial))
        {
            var order = item.PropertyName.Split('.')
                .Aggregate((Expression)parameter, Expression.PropertyOrField);
            if (!order.Type.IsValueType || Nullable.GetUnderlyingType(order.Type) != null)
            {
                var preOrder = Expression.Condition(
                        Expression.Equal(order, Expression.Constant(null, order.Type)),
                        Expression.Constant(1), Expression.Constant(0));
                expression = CallOrderBy(expression, Expression.Lambda(preOrder, parameter), item.Direction, initial);
                initial = false;
            }
            expression = CallOrderBy(expression, Expression.Lambda(order, parameter), item.Direction, initial);
            initial = false;
        }
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(expression);
    }

    private static Expression CallOrderBy(Expression source, LambdaExpression selector, SortDirection direction, bool initial)
    {
        return Expression.Call(
            typeof(Queryable), GetMethodName(direction, initial),
            new Type[] { selector.Parameters[0].Type, selector.Body.Type },
            source, Expression.Quote(selector));
    }

    private static string GetMethodName(SortDirection direction, bool initial)
    {
        return direction == SortDirection.Ascending ?
            (initial ? "OrderBy" : "ThenBy") :
            (initial ? "OrderByDescending" : "ThenByDescending");
    }

    private static IEnumerable<OrderByInfo> ParseOrderBy(string orderBy, bool initial)
    {
        if (String.IsNullOrEmpty(orderBy))
            yield break;

        string[] items = orderBy.Split(',');

        foreach (string item in items)
        {
            string[] pair = item.Trim().Split(' ');

            if (pair.Length > 2)
                throw new ArgumentException(String.Format("Invalid OrderBy string '{0}'. Order By Format: Property, Property2 ASC, Property2 DESC", item));

            string prop = pair[0].Trim();

            if (String.IsNullOrEmpty(prop))
                throw new ArgumentException("Invalid Property. Order By Format: Property, Property2 ASC, Property2 DESC");

            SortDirection dir = SortDirection.Ascending;

            if (pair.Length == 2)
                dir = ("desc".Equals(pair[1].Trim(), StringComparison.OrdinalIgnoreCase) ? SortDirection.Descending : SortDirection.Ascending);

            yield return new OrderByInfo() { PropertyName = prop, Direction = dir, Initial = initial };

            initial = false;
        }

    }

    private class OrderByInfo
    {
        public string PropertyName { get; set; }
        public SortDirection Direction { get; set; }
        public bool Initial { get; set; }
    }

    private enum SortDirection
    {
        Ascending = 0,
        Descending = 1
    }
}

推荐答案

我理解的方式,您有一个DateTime属性(ley称其为Date),而不是常规排序

.OrderBy(x => x.Date)

拥有

之类的东西
var baseDate = DateTime.Today;

您想首先按升序排序未来值,然后按降序顺序排列过去的值.

可以以以下通用方式(在Linq中使用对象以及EF)来实现:

.OrderBy(x => x.Date >= baseDate ? x.Date : DateTime.MaxValue)
.ThenByDescending(x => x.Date >= baseDate ? DateTime.MinValue : x.Date)

要动态实现,您可以在实现方法中插入以下内容:

if (order.Type == typeof(DateTime)) // && some other special condition
{
    var condition = Expression.GreaterThanOrEqual(
        order, Expression.Constant(DateTime.Today));
    var order1 = Expression.Condition(condition,
        order, Expression.Constant(DateTime.MaxValue));
    var order2 = Expression.Condition(condition,
        Expression.Constant(DateTime.MinValue), order);
    expression = CallOrderBy(expression,
        Expression.Lambda(order1, parameter), SortDirection.Ascending, initial);
    expression = CallOrderBy(expression,
        Expression.Lambda(order2, parameter), SortDirection.Descending, false);
    initial = false;
    continue;
}

本文地址:https://www.itbaoku.cn/post/1556885.html

问题描述

I had another question similar answered here on NULL values being last on an order by.

Keep NULL rows last on Dynamic Linq Order By

I would also like to see if I can do the same thing with a Date column with the following criteria.

  1. All items with all end dates on the current date and up at the top, sorted by the most recent upcoming event
  2. Followed by all past events using the end date and comparing to the current date with the most recent end date passed and down. I do something similar in pure SQL at the moment.

            (CASE 
                WHEN ev.EndDate >= GETDATE() THEN 1
                ELSE 2
            END) ASC,
            (CASE
                WHEN ev.EndDate >= GETDATE() THEN ev.EndDate
                ELSE ev.StartDate
            END) ASC,
    

Example: Current Date 3/24/2017

EndDate

3/25/2017

4/15/2017

7/29/2017

3/23/2017

2/22/2016

Current Code

public static class OrderByHelper
{
    public static IOrderedQueryable<T> ThenBy<T>(this IEnumerable<T> source, string orderBy)
    {
        return source.AsQueryable().ThenBy(orderBy);
    }

    public static IOrderedQueryable<T> ThenBy<T>(this IQueryable<T> source, string orderBy)
    {
        return OrderBy(source, orderBy, false);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IEnumerable<T> source, string orderBy)
    {
        return source.AsQueryable().OrderBy(orderBy);
    }

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string orderBy)
    {
        return OrderBy(source, orderBy, true);
    }

    private static IOrderedQueryable<T> OrderBy<T>(IQueryable<T> source, string orderBy, bool initial)
    {
        if (string.IsNullOrWhiteSpace(orderBy))
            orderBy = "ID DESC";
        var parameter = Expression.Parameter(typeof(T), "x");
        var expression = source.Expression;
        foreach (var item in ParseOrderBy(orderBy, initial))
        {
            var order = item.PropertyName.Split('.')
                .Aggregate((Expression)parameter, Expression.PropertyOrField);
            if (!order.Type.IsValueType || Nullable.GetUnderlyingType(order.Type) != null)
            {
                var preOrder = Expression.Condition(
                        Expression.Equal(order, Expression.Constant(null, order.Type)),
                        Expression.Constant(1), Expression.Constant(0));
                expression = CallOrderBy(expression, Expression.Lambda(preOrder, parameter), item.Direction, initial);
                initial = false;
            }
            expression = CallOrderBy(expression, Expression.Lambda(order, parameter), item.Direction, initial);
            initial = false;
        }
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(expression);
    }

    private static Expression CallOrderBy(Expression source, LambdaExpression selector, SortDirection direction, bool initial)
    {
        return Expression.Call(
            typeof(Queryable), GetMethodName(direction, initial),
            new Type[] { selector.Parameters[0].Type, selector.Body.Type },
            source, Expression.Quote(selector));
    }

    private static string GetMethodName(SortDirection direction, bool initial)
    {
        return direction == SortDirection.Ascending ?
            (initial ? "OrderBy" : "ThenBy") :
            (initial ? "OrderByDescending" : "ThenByDescending");
    }

    private static IEnumerable<OrderByInfo> ParseOrderBy(string orderBy, bool initial)
    {
        if (String.IsNullOrEmpty(orderBy))
            yield break;

        string[] items = orderBy.Split(',');

        foreach (string item in items)
        {
            string[] pair = item.Trim().Split(' ');

            if (pair.Length > 2)
                throw new ArgumentException(String.Format("Invalid OrderBy string '{0}'. Order By Format: Property, Property2 ASC, Property2 DESC", item));

            string prop = pair[0].Trim();

            if (String.IsNullOrEmpty(prop))
                throw new ArgumentException("Invalid Property. Order By Format: Property, Property2 ASC, Property2 DESC");

            SortDirection dir = SortDirection.Ascending;

            if (pair.Length == 2)
                dir = ("desc".Equals(pair[1].Trim(), StringComparison.OrdinalIgnoreCase) ? SortDirection.Descending : SortDirection.Ascending);

            yield return new OrderByInfo() { PropertyName = prop, Direction = dir, Initial = initial };

            initial = false;
        }

    }

    private class OrderByInfo
    {
        public string PropertyName { get; set; }
        public SortDirection Direction { get; set; }
        public bool Initial { get; set; }
    }

    private enum SortDirection
    {
        Ascending = 0,
        Descending = 1
    }
}

推荐答案

The way I understand, you have a DateTime property (ley call it Date), and instead of regular sort

.OrderBy(x => x.Date)

having something like

var baseDate = DateTime.Today;

you want to sort the future values first in ascending order followed by the past values in descending order.

It can be achieved in the following generic way (works in LINQ to Objects as well as EF):

.OrderBy(x => x.Date >= baseDate ? x.Date : DateTime.MaxValue)
.ThenByDescending(x => x.Date >= baseDate ? DateTime.MinValue : x.Date)

To implement that dynamically, you could insert the following inside the implementation method body loop:

if (order.Type == typeof(DateTime)) // && some other special condition
{
    var condition = Expression.GreaterThanOrEqual(
        order, Expression.Constant(DateTime.Today));
    var order1 = Expression.Condition(condition,
        order, Expression.Constant(DateTime.MaxValue));
    var order2 = Expression.Condition(condition,
        Expression.Constant(DateTime.MinValue), order);
    expression = CallOrderBy(expression,
        Expression.Lambda(order1, parameter), SortDirection.Ascending, initial);
    expression = CallOrderBy(expression,
        Expression.Lambda(order2, parameter), SortDirection.Descending, false);
    initial = false;
    continue;
}