Entity Framework + DayOfWeek

问题:

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.

回答1:

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