动态Linq +实体框架：动态选择的日期时间修改 [英] Dynamic Linq + Entity Framework: datetime modifications for dynamic select

问题描述

在进行sql分组之前，我正在尝试找到一种将UTC时间移动到Local的方法。我正在使用System.Linq.Dynamic（这里管理的是 https://github.com/kahanu/System .Linq.Dynamic ）。它非常适合做动态选择，而不需要在编译时将所需的字段。在我们的例子中，我们以UTC为单位存储所有数据。在这个动态选择中，有可能有人想在小时，年，月等做一个groupby。在这种情况下，我们必须将数据移动到当地时间，以防止混淆。

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.

示例：

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

通常在我们的tsql甚至在使用lambda表达式的实体框架中，可以添加所需的偏移量。但是在动态linq选项中，您似乎无法像Linq2Sql那样执行DateTime.AddHours（x）或DateTime.Subtract（x）等日期操作。实体框架6希望您使用 DbFunctions.AddHours（x）等动态linq代码，没有修改，将不会接受DbFunctions没有错误。

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.

示例：

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

返回错误：XXX类型中不存在属性或字段System

（删除命名空间没有帮助）。

Returns an error: No property or field 'System' exists in type XXX
(removing the namespace doesn't help).

使用所需的代码：

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

结果与错误：LINQ to Entities不识别方法'System.DateTime AddHours（Double）'方法，并且此方法不能转换为存储表达式。

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.

我想让SQL在groupby之前执行datetime数学。一旦groupby发生，就不会有UTC的概念，因为用户会看到本地化的结果集。

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.

我怕只是更新我的github fork有一些扩展支持传递实体框架扩展，但在我之前，想看看其他人是否有解决方案或想法。

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.

注意：我由于可能会更改SQL数据存储技术，我没有使用DateTimeOffset。

推荐答案

您可以发布查询表达式使用自定义 ExpressionVisitor ，并将不支持的方法替换为 DbFunctions 等价物。

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

和示例用法： p>

and sample usage:

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

这篇关于动态Linq +实体框架：动态选择的日期时间修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！