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

Question

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

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

从这些表中，我需要生成报告，我希望使用LINQ来实现这一点。我希望能够根据这些日期(按月/按年/等等)从各种分组方法中进行选择。

我更喜欢使用LINQ中的组命令，该命令针对这些分组方法之一。为了提高性能，我希望在数据库中执行分组，而不是先将所有数据拖到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框架的一部分而提供的一些更常见的方法。除此之外，它将不知道如何进行翻译。

如果您必须保持db模型的原样：

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

如果您可以选择更改db模型：

最好对数据使用标准的DateTime列类型。然后，您可以将列建模为System.DateTime，并使用它的方法( L2S理解这些方法)。您可以通过修改表本身或提供一个执行转换的视图，并让L2S与视图进行交互来实现这一点。

Update：由于您需要保留当前模型，所以您需要转换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之前拦截它，您可以在L2S前面使用作为代理的create your own IQueryable provider。您将在Execute的实现中执行上述替换，然后将新的查询表达式传递给L2S提供程序。

Answer 2

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

希望能帮上忙。

Answer 3

找到了对一些现有代码的引用，该代码实现了IQueryable提供程序，就像迈克尔建议的那样。

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

我认为，假设代码工作正常，另一个挥之不去的问题是，对于每个包含日期的类型，都必须有一个表达式属性。

避免这样做的结果代码看起来有点麻烦(尽管它可以通过将计算放在一个方法中来避免您试图避免的那种错误)：

群体表达：

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

方法返回组表达式：

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

我不完全确定嵌套的.Invoke是否会导致Expandable表达式的问题，或者这个概念是否需要进一步调整，但该代码似乎为为简单的数学表达式构建自定义IQueryProvider提供了一种替代方案。