实体框架+ DayOfWeek [英] Entity Framework + DayOfWeek
问题描述
使用 System.Linq.Dynamic （在这里管理 https://github.com/kahanu/System.Linq.Dynamic ），我试图捕获 DayOfWeek 在 DateTime 用于使用实体框架6（或更高版本）进行聚合。
以前要求类似的东西，这有助于很多，动态Linq +实体框架：动态选择的日期时间修改
实体框架支持使用
获取
DayOfWeek pre $ SqlFunctions.DatePart（dw，datetime？）
或者我们可以使用类似
DbFunctions.DiffDays（date？date？）
想法：
我发现这很有趣，我喜欢它，因为它不使用可能保留的 SqlFunctions 我局限于SQL Server。此外，开发人员是控制一周中的第一天，而无需查询SQL Server属性来查找其配置（第一天）。
对于实验目的，我一直在尝试在 VisitMember（）中覆盖：
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？））;
}
返回base.VisitMember（node）;
}
使用上面的想法，我想我可以包装这个表达式，然后应用模式值到输入时间，但我甚至不能再得到这个表达式。
我觉得我缺少表达式如何构建的基本部分。
参数类型不匹配
System.Linq.Expressions.Expression.Bind（MemberInfo member，Expression expression）
在System.Linq.Expressions.ExpressionVisitor.Visit [T]（ReadOnlyCollection 1个节点，Func 2 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节点）
在QueryableExtensions.DbFunctionsBinder.VisitMethodCall（MethodCallExpression节点）在Path\QueryableExtensions.cs中：行48
在BindDbFunctions（IQueryable源）在Pa th\QueryableExtensions.cs：第13行
在路径\AggregateHelper.cs中的AggregateHelper.d__15.MoveNext（）：第811行
- - 从前一个位置引发异常的堆栈跟踪结束
在System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess（任务任务）
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification（任务任务）
在System.Runtime.CompilerServices.TaskAwaiter`1.GetResult（）
在AggregationPluginServiceHelper.d__9.MoveNext（）在Path \AggregationPluginServiceHelper.cs：line 199
我知道我可以做到这一点是我在线编译查询。工作正常但是这是特别使用动态库。
示例用法是：
var grouping = select.GroupBy（new（DateTimeColumn.DayOfWeek），it）;
有没有更好的方法来获得星期几？我知道这可能在文化上有所不同，所以做星期天的模式（上面的链接想法）相信是正确的方法。
所以你基本上需要转换一个表达式，如
expr.DayOfWeek
to
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））;
返回dayOfWeek;
}
return base.VisitMember（node）;
}
更新：可以通过使用一个编译时的原型表达式，使用一个小的辅助工具将实际值替换参数：
public static class ExpressionUtils
{
public static Expression< Func< T，TResult>> Expr< T，TResult>（表达< Func< T，TResult> e）=> Ë;
public static Expression< Func< T1，T2，TResult>> Expr< T1，T2，TResult>（表达< Func< T1，T2，TResult> e）=> Ë;
public static Expression< Func< T1，T2，T3，TResult>> Expr （表达< Func< T1，T2，T3，TResult> e）=> Ë;
public static Expression< Func< T1，T2，T3，T4，TResult>> Expr （表达< Func< T1，T2，T3，T4，TResult> 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（xp，xv））;
}
public static Expression ReplaceParameter（此表达式表达式，ParameterExpression源，表达式目标）
{
返回新的ParameterReplacer {Source = source，Target = target} .Visit（expression） ;
}
class ParameterReplacer：ExpressionVisitor
{
public ParameterExpression Source;
public Expression Target;
protected override表达式访问参数（ParameterExpression节点）
{
return node == Source？目标：base.VisitParameter（node）;
}
}
}
这与C ＃6静态导入功能使实现更加简单易读。
例如：
使用静态System.Linq.Expressions。表达;
使用静态ExpressionUtils;
该方法现在看起来像这样：
{
if（node.Type == typeof（DayOfWeek））
{
保护重写表达式VisitMember（MemberExpression node）返回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 ：
保护覆盖表达式VisitMethodCall（MethodCallExpression节点）
{
if node.Object！= null&&& Node.Object.Type == typeof（DateTime）
{
if（node.Method.Name ==AddHours）
{
返回Expr（（DateTime timeValue，double addValue）=>
DbFunctions.AddHours（timeValue，（int）addValue）.Value）
.WithParameters（Visit（node.Object），Visit node.Arguments [0]））;
}
}
return base.VisitMethodCall（node）;
}
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).
Previously asked for something similar, which helped a lot, Dynamic Linq + Entity Framework: datetime modifications for dynamic select
Entity Framework supports getting the DayOfWeek using the
SqlFunctions.DatePart("dw", datetime?)
or we could do something a little more desired using something like
DbFunctions.DiffDays(date?, date?).
Idea:
Getting the DayOfWeek in Linq to Entities
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).
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
Argument types do not match
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 Path\QueryableExtensions.cs:line 48
at BindDbFunctions(IQueryable source) in Path\QueryableExtensions.cs:line 13
at AggregateHelper.d__15.MoveNext() in Path\AggregateHelper.cs:line 811--- End of stack trace from previous location where exception was thrown
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 Path\AggregationPluginServiceHelper.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.
Example usage would be:
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
to
var firstSunday = new DateTime(1753, 1, 7);
(DayOfWeek)(((int)DbFunctions.DiffDays((DateTime?)firstSunday, (DateTime?)expr)) % 7)
Here is how you can do that:
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);
}
}
}
This, combined with the C#6 static import feature makes the implementation much simpler and readable.
For instance:
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);
}
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屋！
