TravisWhidden TravisWhidden - 9 months ago 142
C# Question

Entity Framework + DayOfWeek

Using the

(managed here ), I am trying to capture the
field found on the
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
using the

SqlFunctions.DatePart("dw", datetime?)

or we could do something a little more desired using something like

DbFunctions.DiffDays(date?, date?).


Getting the DayOfWeek in Linq to Entities

I found this quite interesting, and I like it because it doesn’t use the
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

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 Source

So you basically need to convert an expression like



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(
                typeof(DbFunctions), "DiffDays", Type.EmptyTypes,
                Expression.Constant(firstSunday, typeof(DateTime?)),
                Expression.Convert(expr, typeof(DateTime?))),
        var dayOfWeek = Expression.Convert(
            Expression.Modulo(diffDays, Expression.Constant(7)),
        return dayOfWeek;
    return base.VisitMember(node);