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

问题描述

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