Dynamic Linq + Entity Framework: Date and Time Changes for Dynamic Selection

Question

I am trying to find a way to move UTC time to local before doing sql grouping. I am using System.Linq.Dynamic (managed here https://github.com/kahanu/System.Linq.Dynamic ). It is great for performing dynamic selections without having to compile the required fields during compilation. In our case, we save all dates in UTC. In this dynamic choice, it is possible that someone wants to make a group in Hour, year, month, etc. In this case, we must move the data to local time to prevent confusion.

Example:

var select = queryable.Select(string.Format("new ({0}, {1})", datetimeColumn, someOtherColumn));

Usually in our tsql or even in the entity infrastructure using lambda expressions you can add the desired offset. But in the linq dynamic option, it seems you cannot perform any date operations like DateTime.AddHours (x) or DateTime.Subtract (x), as you could with Linq2Sql. Entity Framework 6 wants you to use DbFunctions.AddHours (x) etc. However, dynamic linq code without changes will not accept 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 System field exists in type XXX
(deleting the namespace does not help).

Using the desired code:

var select = queryable.Select(string.Format("new ({0}.AddHours(7), {1})", datetimeColumn, someOtherColumn));

Error results: LINQ to Entities does not recognize the 'System.DateTime AddHours (Double)' method, and this method cannot be translated into a storage expression.

, SQL datetime groupby. , UTC, .

, github , , , , - .

. DateTimeOffset - SQL.

Answer 1

ExpressionVisitor DbFunctions.

, :

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

:

var select = queryable
    .Select(string.Format("new ({0}.AddHours(7), {1})", datetimeColumn, someOtherColumn))
    .BindDbFunctions();