使用自定义日期/时间格式为数据库实施 LINQ to SQL 表达式

Question

我正在使用 MS-SQL 数据库，其中的表使用存储为整数的自定义日期/时间格式。该格式保持时间顺序，但与刻度不是一对一的。可以从自定义格式到小时/天/月/等进行简单的转换。例如，我可以使用 SQL 语句导出月份：

SELECT ((CustomDateInt / 60 / 60 / 24) % 13) AS Month FROM HistoryData

从这些表中，我需要生成报告，并且我想使用LINQ 到 SQL。我希望能够根据这些日期（按月/按年/等）从各种分组方法中进行选择。

我更愿意在 LINQ 中使用针对这些分组方法之一的 group 命令。为了提高性能，我希望在数据库中执行分组，而不是先将所有数据拉入 POCO 对象，然后再对它们进行自定义分组。例如：

var results = from row in myHistoryDataContext.HistoryData
              group row by CustomDate.GetMonth(row.CustomDateInt) into grouping
              select new int?[] { grouping.Key , grouping.Count() }

如何实现分组函数（如 CustomDate.GetMonth），以便它们自动转换为 SQL 命令并在数据库中执行？我是否需要将它们作为 Func对象或 Expression<> 对象提供，或者通过其他方式提供？

Answer 1

您不能编写一个方法并期望 L2S 自动知道如何采用您的方法并将其转换为 SQL。 L2S 了解作为 .NET 框架的一部分为原始类型提供的一些更常见的方法。除此之外，它将不知道如何执行翻译。

如果您必须保持数据库模型不变：

您可以定义与自定义格式交互的方法并在查询中使用它们。但是，您必须帮助 L2S 进行翻译。为此，您需要在为查询生成的表达式树中查找对方法的调用，并将它们替换为 L2S 可以翻译的实现。实现此目的的一种方法是提供代理 IQueryProvider 实现，该实现检查给定查询的表达式树并执行替换，然后将其传递给 L2S IQueryProvider 进行翻译和执行。 L2S 将看到的表达式树可以转换为 SQL，因为它只包含方法定义中使用的简单算术运算。

如果您可以选择更改数据库模型：

您最好为数据使用标准 DateTime 列类型。然后，您可以将该列建模为 System.DateTime 并使用其方法（L2S 可以理解）。您可以通过修改表本身或提供执行转换的视图并让 L2S 与视图交互来实现此目的。

更新：
由于您需要保留当前的模型，因此您需要将您的方法转换为 L2S。我们的目标是用 lambda L2S 可以转换来替换对 L2S 查询中某些特定方法的调用。对这些方法的所有其他调用当然都会正常执行。下面是您可以执行此操作的一种方法的示例...

static class DateUtils
{
    public static readonly Expression<Func<int, int>> GetMonthExpression = t => (t / 60 / 60 / 24) % 13;
    static readonly Func<int, int> GetMonthFunction;

    static DateUtils()
    {
        GetMonthFunction = GetMonthExpression.Compile();
    }

    public static int GetMonth(int t)
    {
        return GetMonthFunction(t);
    }
}

这里我们有一个类，它定义了一个 lambda 表达式，用于从整数时间获取月份。为了避免定义两次数学，您可以编译表达式，然后从 GetMonth 方法调用它，如下所示。或者，您可以获取 lambda 的主体并将其复制到 GetMonth 方法的主体中。这将跳过表达式的运行时编译，并且可能执行得更快——取决于您的喜好。

请注意，GetMonthExpression lambda 的签名与 GetMonth 方法完全匹配。接下来，我们将使用 System.Linq.Expressions.ExpressionVisitor 检查查询表达式，查找对 GetMonth 的调用，并将它们替换为我们的 lambda，替换为 t< /code> 与 GetMonth 第一个参数的值。

class DateUtilMethodCallExpander : ExpressionVisitor
{
    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        LambdaExpression Substitution = null;

        //check if the method call is one we should replace
        if(node.Method.DeclaringType == typeof(DateUtils))
        {
            switch(node.Method.Name)
            {
                case "GetMonth": Substitution = DateUtils.GetMonthExpression;
            }
        }

        if(Substitution != null)
        {
            //we'd like to replace the method call; we'll need to wire up the method call arguments to the parameters of the lambda
            var Replacement = new LambdaParameterSubstitution(Substitution.Parameters, node.Arguments).Visit(Substitution.Body);
            return Replacement;
        }

        return base.VisitMethodCall(node);
    }
}

class LambdaParameterSubstitution : ExpressionVisitor
{
    ParameterExpression[] Parameters;
    Expression[] Replacements;

    public LambdaParameterExpressionVisitor(ParameterExpression[] parameters, Expression[] replacements)
    {
        Parameters = parameters;
        Replacements = replacements;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        //see if the parameter is one we should replace
        int p = Array.IndexOf(Parameters, node);
        if(p >= 0)
        {
            return Replacements[p];
        }

        return base.VisitParameter(node);
    }
}

这里的第一个类将访问查询表达式树并查找对 GetMonth（或任何其他需要替换的方法）的引用并替换方法调用。替换部分由第二个类提供，它检查给定的 lambda 表达式并替换对其参数的引用。

转换查询表达式后，L2S 将永远不会看到对您的方法的调用，并且它现在可以按预期执行查询。

为了以方便的方式在查询到达 L2S 之前拦截查询，您可以 创建您自己的 IQueryable 提供程序，用作 L2S 前面的代理。您将在 Execute 的实现中执行上述替换，然后将新的查询表达式传递给 L2S 提供程序。

Answer 2

我认为您可以在 DataContext 中注册自定义函数并在 linq 查询中使用它。在这篇文章中得到了很好的解释：http://msdn.microsoft.com/en -us/library/bb399416.aspx

希望有帮助。

Answer 3

找到了对一些现有代码的引用，这些代码实现了 Michael 建议的 IQueryable 提供程序。

http://tomasp.net/blog/linq-expand.aspx

我认为假设代码有效，另一个挥之不去的问题是您必须为包含日期的每种类型拥有一个 Expression 属性。

避免这样做的结果代码似乎有点麻烦（尽管它可以避免您试图通过将计算放入方法中来避免的那种错误）：

Group Expression:

group row by CustomDate.GetMonth(row, x => x.customdate).Compile().Invoke(row)

Method to Return Group Expression:

public class CustomDate
{
        public static Expression<Func<TEntity, int>> GetMonth<TEntity>(TEntity entity, Func<TEntity, int> func)
        {
            return x => ((func.Invoke(entity)/60/60/24)%13);
        }
}

I'm not完全确定嵌套的 .Invoke 是否会导致 Expandable 表达式出现问题，或者是否需要对概念进行更多调整，但该代码似乎提供了替代方案为简单的数学表达式构建自定义 IQueryProvider。

Answer 4

似乎没有任何方法可以指示 LINQ-to-SQL 调用 SQL UDF。但是，我相信您可以在 System.Linq.Expressions.Expression 树中封装可重用的 C# 实现...

public class CustomDate {
    public static readonly Expression<Func<int, int>> GetMonth = 
                customDateInt => (customDateInt / 60 / 60 / 24) % 13;
}

var results = from row in myHistoryDataContext.HistoryData
              group row by CustomDate.GetMonth(row.CustomDateInt) into grouping
              select new int?[] { grouping.Key , grouping.Count() }