实体框架 + DayOfWeek [英] Entity Framework + DayOfWeek
问题描述
使用 System.Linq.Dynamic(在此处管理 https://github.com/kahanu/System.Linq.Dynamic )，我正在尝试使用实体捕获 DateTime 上的 DayOfWeek 字段以用于聚合目的框架 6(或更高版本).
Using the System.Linq.Dynamic (managed here https://github.com/kahanu/System.Linq.Dynamic ), I am trying to capture the DayOfWeek field found on the DateTime for aggregation purposes using Entity Framework 6 (or greater).
以前要求类似的东西，这有很大帮助，Dynamic Linq + Entity Framework:动态选择的日期时间修改
Previously asked for something similar, which helped a lot, Dynamic Linq + Entity Framework: datetime modifications for dynamic select
Entity Framework 支持使用
Entity Framework supports getting the DayOfWeek using the
SqlFunctions.DatePart("dw", datetime?)
或者我们可以使用类似
DbFunctions.DiffDays(date?, date?).
想法:
我发现这很有趣，我喜欢它，因为它不使用可能使我受限于 SQL Server 的 SqlFunctions.此外，开发人员可以控制一周的第一天是什么，而无需查询 SQL Server 属性来查找其配置方式(第一天).
I found this quite interesting, and I like it because it doesn’t use the SqlFunctions which might keep me confined to SQL Server. Plus the developer is control of what the first day of the week is without having to query the SQL Server properties to find how its configured (for first day).
出于实验目的，我一直在尝试在 VisitMember() 覆盖中实现这一点:
For experimental purposes, I have been trying to implement this in the VisitMember() override:
protected override Expression VisitMember(MemberExpression node)
{
if (node.Type == typeof(System.DayOfWeek))
{
var firstSunday = new DateTime(1753, 1, 7);
var firstSundayExpression = Expression.Constant(firstSunday, typeof(DateTime?));
var timeValue = node.Expression;
if (timeValue.Type != typeof(DateTime?)) timeValue = Expression.Convert(timeValue, typeof(DateTime?));
var methodCall = Expression.Call(
typeof(DbFunctions), "DiffDays", Type.EmptyTypes, firstSundayExpression, timeValue);
return Expression.Convert(methodCall, typeof(int?));
}
return base.VisitMember(node);
}
使用上面的想法，我想我可以包装这个表达式，然后将模值应用于输入时间，但我什至无法让这个表达式更进一步.
Using the idea above, I think I could wrap this expression and then apply the modulus value to the input time, but I cant even get this expression to go any further.
我觉得我错过了如何构建表达式的基本部分.我遇到的错误
I feel like I am missing a fundamental part of how expressions are build. The error I am getting with this
参数类型不匹配
在 System.Linq.Expressions.Expression.Bind(MemberInfo 成员，表达式表达式)
在 System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection1 个节点，Func2 个 elementVisitor)
在 System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression 节点)
在 System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 节点)
在 System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression 节点)
在 System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider 节点)
在 System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression 节点)
在 PathQueryableExtensions.cs:line 48 中的 QueryableExtensions.DbFunctionsBinder.VisitMethodCall(MethodCallExpression 节点)
在 PathQueryableExtensions.cs:line 13 中的 BindDbFunctions(IQueryable source)
在 PathAggregateHelper.cs:line 811 中的 AggregateHelper.d__15.MoveNext()
at System.Linq.Expressions.Expression.Bind(MemberInfo member, Expression expression)
at System.Linq.Expressions.ExpressionVisitor.Visit[T](ReadOnlyCollection1 nodes, Func2 elementVisitor)
at System.Linq.Expressions.ExpressionVisitor.VisitMemberInit(MemberInitExpression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
at System.Linq.Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes)
at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
at QueryableExtensions.DbFunctionsBinder.VisitMethodCall(MethodCallExpression node) in PathQueryableExtensions.cs:line 48
at BindDbFunctions(IQueryable source) in PathQueryableExtensions.cs:line 13
at AggregateHelper.d__15.MoveNext() in PathAggregateHelper.cs:line 811
--- 从先前抛出异常的位置结束堆栈跟踪
--- End of stack trace from previous location where exception was thrown
在 System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(任务任务)
在 System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(任务任务)
在 System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
在 PathAggregationPluginServiceHelper.cs:line 199 中的 AggregationPluginServiceHelper.d__9.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at AggregationPluginServiceHelper.d__9.MoveNext() in PathAggregationPluginServiceHelper.cs:line 199
我知道我可以做到这一点是我编写了内联编译的查询.这很好用.但这是专门使用动态库的.
I know I can do this is I wrote the query inline-compiled. That works fine. But this is specifically using the dynamic library.
示例用法如下:
var grouping = select.GroupBy("new (DateTimeColumn.DayOfWeek)", "it");
有没有更好的方法来获取星期几?我知道这可能在文化上有所不同，所以我相信与星期日不同的日子模数(上面的链接想法)是正确的方法.
Is there a better way to get the Day of the week? I know that may be culturally different, so doing the modulus of the days different from Sunday (link idea above) I believe is the correct method.
推荐答案
所以你基本上需要转换成这样的表达式
So you basically need to convert an expression like
expr.DayOfWeek
到
var firstSunday = new DateTime(1753, 1, 7);
(DayOfWeek)(((int)DbFunctions.DiffDays((DateTime?)firstSunday, (DateTime?)expr)) % 7)
您可以这样做:
protected override Expression VisitMember(MemberExpression node)
{
if (node.Type == typeof(DayOfWeek))
{
var expr = node.Expression;
var firstSunday = new DateTime(1753, 1, 7);
var diffDays = Expression.Convert(
Expression.Call(
typeof(DbFunctions), "DiffDays", Type.EmptyTypes,
Expression.Constant(firstSunday, typeof(DateTime?)),
Expression.Convert(expr, typeof(DateTime?))),
typeof(int));
var dayOfWeek = Expression.Convert(
Expression.Modulo(diffDays, Expression.Constant(7)),
typeof(DayOfWeek));
return dayOfWeek;
}
return base.VisitMember(node);
}
更新:该过程可以通过使用编译时原型表达式来简化，使用小型辅助实用程序将参数替换为实际值:
Update: The process can be simplified by using a compile time prototype expressions, replacing the parameters with actual values using a small helper utility:
public static class ExpressionUtils
{
public static Expression<Func<T, TResult>> Expr<T, TResult>(Expression<Func<T, TResult>> e) => e;
public static Expression<Func<T1, T2, TResult>> Expr<T1, T2, TResult>(Expression<Func<T1, T2, TResult>> e) => e;
public static Expression<Func<T1, T2, T3, TResult>> Expr<T1, T2, T3, TResult>(Expression<Func<T1, T2, T3, TResult>> e) => e;
public static Expression<Func<T1, T2, T3, T4, TResult>> Expr<T1, T2, T3, T4, TResult>(Expression<Func<T1, T2, T3, T4, TResult>> e) => e;
public static Expression WithParameters(this LambdaExpression expression, params Expression[] values)
{
return expression.Parameters.Zip(values, (p, v) => new { p, v })
.Aggregate(expression.Body, (e, x) => e.ReplaceParameter(x.p, x.v));
}
public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
{
return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
}
class ParameterReplacer : ExpressionVisitor
{
public ParameterExpression Source;
public Expression Target;
protected override Expression VisitParameter(ParameterExpression node)
{
return node == Source ? Target : base.VisitParameter(node);
}
}
}
这与 C#6 静态导入功能相结合，使实现更加简单易读.
This, combined with the C#6 static import feature makes the implementation much simpler and readable.
例如:
using static System.Linq.Expressions.Expression;
using static ExpressionUtils;
有问题的方法现在看起来像这样:
The method in question now looks like this:
protected override Expression VisitMember(MemberExpression node)
{
if (node.Type == typeof(DayOfWeek))
{
return Expr((DateTime dateValue1, DateTime dateValue2) =>
(DayOfWeek)(DbFunctions.DiffDays(dateValue1, dateValue2).Value % 7))
.WithParameters(Constant(new DateTime(1753, 1, 7)), Visit(node.Expression));
}
return base.VisitMember(node);
}
以及您上一个关于 AddHours 的问题:
and the one from your previous question about AddHours:
protected override Expression VisitMethodCall(MethodCallExpression node)
{
if (node.Object != null && node.Object.Type == typeof(DateTime))
{
if (node.Method.Name == "AddHours")
{
return Expr((DateTime timeValue, double addValue) =>
DbFunctions.AddHours(timeValue, (int)addValue).Value)
.WithParameters(Visit(node.Object), Visit(node.Arguments[0]));
}
}
return base.VisitMethodCall(node);
}
这篇关于实体框架 + DayOfWeek的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
