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

问题描述

我正在尝试在进行 sql 分组之前找到一种将 UTC 时间移动到本地的方法.我正在使用 System.Linq.Dynamic(在此处管理 https://github.com/kahanu/System.Linq.Dynamic ).它非常适合进行动态选择，而在编译时没有必填字段.在我们的例子中，我们以 UTC 格式存储所有日期时间.在这种动态选择中，可能有人想对小时、年、月等进行分组.在这种情况下，我们必须将数据移动到本地时间，以防止混淆.

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).Entity Framework 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 类型中不存在属性或字段系统"
(删除命名空间没有帮助).

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 之前执行日期时间数学.一旦 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);
        }
    }
}

和示例用法:

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

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