Entity Framework + DayOfWeek

Question

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.

Frady

is there a reason you just not calculating this after you do your query? It might just be easier to do that after your call your dynamic linq – Jejunum

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

Demulsify

Again you come through for me. That worked right out the gate. This seems incredibility powerful. Trying to get my head around this. Seems like some key things to understand is what Expression.Call, vs Expression.Convert is used for. Thank you very much! – Frady

You are welcome:) The most important thing to remember is that you should not change the data type of the expression you are replacing. Convert is just the Expression equivalent of C# cast operator. Cheers. – Demulsify

Ahh, got it. I just added another function... re-read your hint. I was returning a (DateTime?), but in the case of DateTime.Date it expected a (DateTime). Must pay attention to the type details for sure. Much appreciated. – Frady

Looks like you are going to make more stuff like this, so as a bonus I've added to the answer a simpler and less error prone way of doing such things. – Demulsify

That is terrific! For example, one of the ones I created turned into if (node.Member.Name == "Date") { return Expr((DateTime dateValue1) => DbFunctions.TruncateTime(dateValue1).Value).WithParameters(Visit(node.Expression)); } – Frady

In the process of learning way more about expressions here, I also got to touch on the new C#6 features. Way less error prone for sure too. Much appreciated the update. So cool. – Frady

@Frady Cool :) – Demulsify