TravisWhidden TravisWhidden - 1 month ago 32
C# Question

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](ReadOnlyCollection
1 nodes, Func
2 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.

Answer

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