在Entity Framework Code First中使用搜索字符串搜索DateTime

Question

我有一个MVC项目，该项目将可空DateTime?类型转换为字符串，并将其作为每个模型项输出给用户。如下所示（我的用户界面进行了分页，因此我仅为少量记录执行此计算 - 换句话说，这部分是可以的）：

foreach (DocumentEvent item in Model.items)
    @(item?.TimeUtc?.ToString() ?? "N/A")

我希望添加搜索功能。我尝试了以下搜索方法，但这种方法不够高效，因为AsEnumerable会实现我的列表，并且我现在处于C#环境中，需要枚举每个记录：

using (var context = new ClientEventsContext())
    var items = context.Events.AsEnumerable().Where(x => {
        (x?.TimeUtc?.ToString() ?? "N/A").Contains(model.search)
    });

相反，我希望利用我的SQL Server数据库。如何构建一个适合SQL Server的查询来代替上面的代码中的AsEnumerable，并产生与当前逻辑相同的结果？

- Alexandru

---

@Fals，你不能这样做，因为Entity Framework无法将(x?.TimeUtc?.ToString() ?? "N/A").Contains(model.search)转换成SQL查询语句，所以会引发异常。 - Alexandru

一般情况下，我们使用范围搜索来查询日期和时间，并将它们保留在其原始数据格式中。"包含"字符串搜索将毫无用处。我可能会搜索 '0' 或者 '/'。 - Matt Johnson-Pint

@Alexandru 你使用的是哪个版本的Entity Framework？ - yosbel

@GertArnold 它没有被索引，但是你说得很好。 - Alexandru

@MattJohnson 当你说要考虑日期的不同表示方式时，你是指数据库内部还是Web服务器内部，因为在这种情况下，两者都应最终绑定到一组格式。 - Alexandru

Answer 1

这里是如何构建和使用与LINQ to Entities兼容的日期转换为字符串的M/d/yyyy h:mm:ss tt格式。不要将这个巨大的东西嵌入查询中，我将使用自定义的“标记”方法，并使用ExpressionVisitor绑定实现。这样，您可以进行实验并根据需要更改格式（甚至添加一些控制参数）而不影响查询的可读性。

首先，是实现代码：

public static class EFExtensions
{
    public static string ToCustomDateFormat(this DateTime value)
    {
        // Should never happen
        throw new InvalidOperationException();
    }

    public static IQueryable<T> ApplyCustomDateFormat<T>(this IQueryable<T> source)
    {
        var expression = new CustomDateFormatBinder().Visit(source.Expression);
        if (source.Expression == expression) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    class CustomDateFormatBinder : ExpressionVisitor
    {
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(EFExtensions) && node.Method.Name == "ToCustomDateFormat")
            {
                var date = Visit(node.Arguments[0]);
                var year = DatePart(date, v => DbFunctions.Right("0000" + v.Year, 4));
                var month = DatePart(date, v => v.Month.ToString());
                var day = DatePart(date, v => v.Day.ToString());
                var hour = DatePart(date, v => (1 + (v.Hour + 11) % 12).ToString());
                var minute = DatePart(date, v => DbFunctions.Right("0" + v.Minute, 2));
                var second = DatePart(date, v => DbFunctions.Right("0" + v.Second, 2));
                var amPM = DatePart(date, v => v.Hour < 12 ? "AM" : "PM");
                var dateSeparator = Expression.Constant("/");
                var timeSeparator = Expression.Constant(":");
                var space = Expression.Constant(" ");
                var result = Expression.Call(
                    typeof(string).GetMethod("Concat", new Type[] { typeof(string[]) }),
                    Expression.NewArrayInit(typeof(string),
                        month, dateSeparator, day, dateSeparator, year, space,
                        hour, timeSeparator, minute, timeSeparator, second, space, amPM));
                return result;    
            }
            return base.VisitMethodCall(node);
        }

        Expression DatePart(Expression date, Expression<Func<DateTime, string>> part)
        {
            var parameter = part.Parameters[0];
            parameterMap.Add(parameter, date);
            var body = Visit(part.Body);
            parameterMap.Remove(parameter);
            return body;
        }

        Dictionary<ParameterExpression, Expression> parameterMap = new Dictionary<ParameterExpression, Expression>();

        protected override Expression VisitParameter(ParameterExpression node)
        {
            Expression replacement;
            return parameterMap.TryGetValue(node, out replacement) ? replacement : node;
        }
    }
}

然后是用法：

var items = context.Events
    .Where(x => x.TimeUtc != null && 
        x.TimeUtc.Value.ToCustomDateFormat().Contains(model.search))
    .ApplyCustomDateFormat();

- Ivan Stoev

---

这是非常优美的代码。我希望我能给你更多的分数。 - Alexandru

不客气，伙计 :) 你的话比一些虚拟点数更有价值！ - Ivan Stoev

Answer 2

我找到了一个解决方案。这个语法似乎可以工作：

using (var context = new ClientEventsContext())
    var items = context.Events.Where(x => {
        x.TimeUtc.HasValue && x.TimeUtc.Value.ToString().Contains(model.search)
    });

- Alexandru

---

@yosoy 对不起，我忘记从问题中复制和粘贴时删除它了，事实上没有它也可以运行。已修改答案。 - Alexandru

即使这个答案目前存在一些烦人的问题。例如，如果我看到这个时间戳，“3/19/2012 7:22:00 AM”，搜索可以使用“2012”或“7:22”，但无法使用“3/19/2012”和“7:22:00”...它必须使用SQL Server DateTime格式或其他什么东西...所以，也许，我需要找到一种方法来编辑我的答案以相同的方式进行格式化，或者采用更好的方法。 - Alexandru

现在我似乎无法弄清楚如何获取数据库的确切查询友好格式。 - Alexandru

@IvanStoev System.Globalization.CultureInfo.CurrentCulture 是 {en-US}。 - Alexandru

@IvanStoev 我已经尝试了很多不同的搜索字符串格式，比如 2012-03 或 2012/03，甚至在月份和日期混淆的情况下也尝试了不同的排列组合。 - Alexandru

Answer 3

另一种解决方案是使用SqlFunctions库将任何内容转换为原始查询，并在SQL级别上执行操作，例如：

using (var context = new ClientEventsContext())
var items = context.Events.Where(x =>
    SqlFunctions.PatIndex(model.search, 
    SqlFunctions.DateName('your pattern here', x?.TimeUtc) ?? "N/A").Value > -1
);

- Fals

---

“StringConvert” 似乎只接受 double 或 decimal 类型的参数？ - Alexandru

@Alexandru 对于每个基本类型都有一个重载。 - Fals

不过好像并不适用于 DateTime?。如果你不相信，可以自己看一下：https://msdn.microsoft.com/zh-cn/library/system.data.objects.sqlclient.sqlfunctions_methods(v=vs.110).aspx 有一个 Checksum 方法，但我不确定如何使用它。你尝试调用的方法可能存在问题。 - Alexandru

@Alexandru 没错，你是正确的。你的解决方案更好，因为 ToString 支持每一种原始类型，并且该解决方案不限于 SqlServer。StringConvert 主要用于有小数位限制的数字格式化。 - Ivan Stoev

@IvanStoev 但是即使我的代码也存在问题，例如，如果我看到这个时间戳 3/19/2012 7:22:00 AM，搜索可以使用 2012 或 7:22，但无法使用 3/19/2012 和 7:22:00...它可能正在使用 SQL Server DateTime 格式或其他什么东西...所以，也许，我需要找到一种方法来编辑我的答案以相同的方式格式化它，或者采用更好的方法。 - Alexandru