sql - 为具有自定义日期/时间格式的数据库实现 LINQ to SQL 表达式

Question

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

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

我需要根据这些表生成报告，并且我想使用 LINQ-to-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 与 View 交互的 View 来实现此目的。

更新:由于您需要保留当前模型，因此您需要将您的方法转换为 L2S。我们的目标是用 L2S 可以翻译的 lambda 替换 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 为 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 之前拦截查询，您可以 create your own IQueryable provider用作 L2S 前面的代理。您将在 Execute 的实现中执行上述替换，然后将新的查询表达式传递给 L2S 提供程序。