Entity Framework + DayOfWeek

c# dynamic-linq entity-framework-6 expression-trees linq

Question

With theSystem.Linq.Dynamic I'm attempting to convey the feeling of (handled here https://github.com/kahanu/System.Linq.Dynamic).DayOfWeek area discovered on theDateTime in order to aggregate data using Entity Framework 6 (or greater).

Earlier requested a comparable item, which was really helpful, Entity Framework and Dynamic Linq: datetime updates for dynamic select

Entity Framework facilitates obtainingDayOfWeek with the

SqlFunctions.DatePart("dw", datetime?)

Alternatively, we may use something more desirable, such as

DbFunctions.DiffDays(date?, date?).  

Idea:

Linq to Entities: Getting the DayOfWeek

This is extremely fascinating, and I like that it avoids theSqlFunctions which might limit me to SQL Server. In addition, the developer has control over the configuration of the first day of the week without having to query the SQL Server attributes (for first day).

I've been attempting to use this for testing reasons in theVisitMember() 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);
 }

I believe I could wrap this expression using the aforementioned concept and then apply the modulus value to the input time, however I can't even get this expression to wrap.

I feel as if I am not understanding how phrases are constructed fundamentally. This is where I'm having trouble.

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 am capable of doing this since I inline-compiled the query. That is effective. However, this uses the dynamic library explicitly.

Usage examples include:

var grouping = select.GroupBy("new (DateTimeColumn.DayOfWeek)", "it");

Is there a more effective approach to determine the day of the week? Since there may be cultural differences, I feel the proper approach is to use the modulus of the days other than Sunday (see the connection suggested above).

1
4
5/23/2017 11:33:15 AM

Accepted Answer

Consequently, you must simply translate a statement like

expr.DayOfWeek

to

var firstSunday = new DateTime(1753, 1, 7);
(DayOfWeek)(((int)DbFunctions.DiffDays((DateTime?)firstSunday, (DateTime?)expr)) % 7)

Here's how to go about it:

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: By combining compile-time prototype expressions and a tiny helper tool to replace the arguments with real values, the procedure may be made simpler:

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

The implementation is significantly more straightforward and understandable as a result of this and the static import feature of C#6.

For illustration:

using static System.Linq.Expressions.Expression;
using static ExpressionUtils;

The technique in question now appears as follows:

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 earlier inquiry aboutAddHours :

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);
}
8
10/27/2016 1:56:19 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow