Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to find a way to move UTC time to Local before doing a sql grouping. I am using the System.Linq.Dynamic (managed here https://github.com/kahanu/System.Linq.Dynamic ). It works great for doing dynamic selects without having at compile time the required fields. In our case, we store all datetimes in UTC. In this dynamic select, its possible that someone would want to do a groupby on the Hour, year, month, etc. We have to move the data to a local time in this case, to prevent confusion.

Example:

var select = queryable.Select(string.Format("new ({0}, {1})", datetimeColumn, someOtherColumn));
Normally in our tsql or even in entity framework using lambda expressions, you can add in your desired offset.  But in the dynamic linq option, it appears that you can't perform any date operations such as DateTime.AddHours(x) or DateTime.Subtract(x) like you could with Linq2Sql.  Entity Framework 6 wants you to use DbFunctions.AddHours(x), etc.  However the dynamic linq code, without modification, will not accept the DbFunctions without error.
Example:
var select = queryable.Select(string.Format("new (System.Data.Entity.DbFunctions.AddHours({0},7) as {0}, {1})", datetimeColumn, someOtherColumn));
Returns an error: No property or field 'System' exists in type XXX

(removing the namespace doesn't help).
Using the desired code:
var select = queryable.Select(string.Format("new ({0}.AddHours(7), {1})", datetimeColumn, someOtherColumn));
Results with error: LINQ to Entities does not recognize the method 'System.DateTime AddHours(Double)' method, and this method cannot be translated into a store expression.
I want to have SQL perform the datetime math prior to the groupby. Once the groupby happens, there is no concept of UTC any longer as the user will see the localized result set.
I'm afraid that Ill just to update my github fork with some extensions to support passing in the entity framework extensions, but before I did, wanted to see if anyone else has a solution or idea.
Note: I am not using DateTimeOffset due to possibilities of changing SQL data store technologies.
You can post process the query expression with custom ExpressionVisitor and replace the unsupported methods with their DbFunctions equivalents.
Here is a starting point just to get the idea:
public static class QueryableExtensions
    public static IQueryable BindDbFunctions(this IQueryable source)
        var expression = new DbFunctionsBinder().Visit(source.Expression);
        if (expression == source.Expression) return source;
        return source.Provider.CreateQuery(expression);
    class DbFunctionsBinder : ExpressionVisitor
        protected override Expression VisitMethodCall(MethodCallExpression node)
            if (node.Object != null && node.Object.Type == typeof(DateTime))
                if (node.Method.Name == "AddHours")
                    var timeValue = Visit(node.Object);
                    var addValue = Visit(node.Arguments.Single());
                    if (timeValue.Type != typeof(DateTime?)) timeValue = Expression.Convert(timeValue, typeof(DateTime?));
                    if (addValue.Type != typeof(int?)) addValue = Expression.Convert(addValue, typeof(int?));
                    var methodCall = Expression.Call(
                        typeof(DbFunctions), "AddHours", Type.EmptyTypes,
                        timeValue, addValue);
                    return Expression.Convert(methodCall, typeof(DateTime));
            return base.VisitMethodCall(node);
and sample usage:
var select = queryable
    .Select(string.Format("new ({0}.AddHours(7), {1})", datetimeColumn, someOtherColumn))
    .BindDbFunctions();
                Fantastic solution.  Thanks for that! It helps me understand the expression better and learned something new.  Excellent solution and worked right out of the box.  I made my slight changes I wanted but allowed me to use the DbFunctions. This also unlocks other potential usages also. Hope this answer helps someone else in the future!
– TravisWhidden
                Oct 6, 2016 at 22:13
                Ivan, any chance you could take a look at this question? Similar in fashion, just stuck on implementation. stackoverflow.com/questions/40271588/entity-framework-dayofweek
– TravisWhidden
                Oct 26, 2016 at 20:52
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.