使用实体框架和Linq按日期动态订购 [英] Dynamic Order By On Date With Entity Framework and Linq
问题描述
我还有另一个类似的问题，关于NULL值在哪个顺序上排在最后.
I had another question similar answered here on NULL values being last on an order by.
我还想看看是否可以使用符合以下条件的Date列来做同样的事情.
I would also like to see if I can do the same thing with a Date column with the following criteria.
- 所有结束日期在当前日期且在顶部的项目，按最近发生的事件排序
-
后跟所有使用结束日期的过去事件，并将当前日期与经过和向下传递的最近结束日期进行比较.我现在在纯SQL中做类似的事情.
- All items with all end dates on the current date and up at the top, sorted by the most recent upcoming event
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,
示例:当前日期2017年3月24日
Example: Current Date 3/24/2017
EndDate
2017/3/25
3/25/2017
4/15/2017
4/15/2017
2017年7月29日
7/29/2017
2017/3/23
3/23/2017
2/22/2016
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属性(将其称为Date)，而不是常规排序
The way I understand, you have a DateTime property (ley call it Date), and instead of regular sort
.OrderBy(x => x.Date)
具有类似
var baseDate = DateTime.Today;
您要先按升序对将来的值进行排序，然后再按降序对过去的值进行排序.
you want to sort the future values first in ascending order followed by the past values in descending order.
可以通过以下通用方式来实现(在LINQ to Objects和EF中都可以使用):
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;
}
这篇关于使用实体框架和Linq按日期动态订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
