mysql datetime 转date_EF Core For MySql查询中使用DateTime.Now作为查询条件的一个小问题...

背景

最近一直忙于手上澳洲线上项目的整体迁移和升级的准备工作，导致博客和公众号停更。本周终于艰难的完成了任务，借此机会，总结一下项目中遇到的一些问题。

EF Core 一直是我们团队中中小型项目常用的 ORM 框架，在使用 SQL Server 作为持久化仓储的场景一下，一直表现还中规中矩。但是在本次项目中，项目使用了 MySql 作为持久化仓储。为了与 EF Core 集成，团队使用了Pomelo.EntityFrameworkCore.MySql作为 EF Core For MySql 的扩展。在开发过程中，团队遇到了各种各样在 SQL Server 场景下没有遇到过的问题，其中最奇怪的，也是隐藏最深的问题，就是将DateTime.Now作为查询条件，产生了非预期的结果。

问题场景

本周在项目升级的过程中，客户反馈了一个问题。

在当前系统的 Dashboard 页面，有一个消息提醒功能，客户可以自定义一些消息，并且指定提醒的日期。客户遇到的问题是通常添加的消息提醒，在指定日期的上午时间段是不会显示，只有在下午时间段才能看到，比如说客户指定 2019 年 10 月 26 号看到一个的消息提醒，但是在 10 月 26 日这天早上 8:00-12:00 这个时间段，系统总是看不到提醒，只有到了下午的时间段才能看到提醒。

PS：这里客户表达的只是个笼统的问题，但问题确实是上午的大部分时间是看不到消息提醒的，但并不是精确到中午 12：00 点这个时间, 所以此处不必过于纠结于具体的时间。

查看问题代码

看到这个问题的时候，我自己也很奇怪，难道代码或者数据库使用了时区，导致查询出现了偏差？

于是我就 Review 了一下此处的查询， 代码如下。

var query = DbContext.CRM_Note_Reminders
    .Include(x => x.CRM_Note)
    .Where(x => !x.CRM_Note.Is_Deleted
             && !x.Is_Deleted
             && x.Reminder_Date.Date
               <= DateTime.Now.Date)
     .ToList();

PS: 这里可能有同学会有疑问，为啥不用DbFunctions.DiffDays? 原因是DbFunctions.DiffDays是 EF Core for SQLServer 的扩展方法，针对 MySql 还没有官方的实现方案。

从这个查询中，我没有看出任何问题，于是我直接借助一些日志工具，将 EF Core 生成的查询语句的输出了出来。

其中 WHERE 条件部分如下：

WHERE (((`x.CRM_Note`.`Is_Deleted` = FALSE)
AND (`x`.`Is_Deleted` = FALSE))
AND (CONVERT(`x`.`Reminder_Date`, date)
  <= CONVERT(CURRENT_TIMESTAMP(), date)))

这里CURRENT_TIMESTAMP()是 MySql 的内置函数，与 SQLServer 的内置函数GETDATE()不同，CURRENT_TIMESTAMP()默认返回的是 UTC 时间。因此我们大概能知道，为什么澳洲客户会遇到上面的场景了。

由于澳洲处于东 10 区，与 UTC 时间有+10 个小时的时差，所以当澳洲上午的 10 点之前，UTC 时间都是在当前澳洲日期的前一天，所以系统中出现了当天的消息提醒在上午时间段不能正常显示的问题。

PS：由于澳洲是分冬令时和夏令时的，夏令时时间要加一个小时，所以实际上客户在每天的 11 点之前都无法看到正确的消息提醒。

深入思考

你这可能会非常奇怪，为什么DateTime.Now会被转化成内置函数CURRENT_TIMESTAMP()，而没有使用我们传入的值DateTime.Now.Date呢？

其实 EF/EF Core 在查询是时候是分 2 个阶段的，一个是组合查询表达式树的阶段，一个是真正的查询阶段。

在组合查询表达式树的阶段，EF/EF Core 只会去组合表达式，而不会去尝试计算表达式的值，所以这个阶段DateTime.Now.Date的值并没有被计算出来， 在进入正常查询阶段的时候， EF/EF Core 会尝试将查询表达式树翻译成 SQL 脚本，这时候由于我们的EF Provider是MySql Provider， 恰巧DateTime.Now可以翻译成 Mysql 的内置函数CURRENT_TIMESTAMP()， 所以这里 EF/EF Core 就跳过了表达式值的计算，直接将其翻译成了对应的内置函数，所以导致生成的 SQL 查询和我们的预期有偏差。

那么我们该如何解决这个问题呢？

解决方案

经过了以上的思考，其实解决这个问题也就很简单了，我们可以将DateTime.Now.Date先计算出来，保存在一个变量中，然后将这个变量传入查询中。

var today = DateTime.Now.Date;

var query = DbContext.CRM_Note_Reminders
     .Include(x => x.CRM_Note)
     .Where(x => !x.CRM_Note.Is_Deleted
             && !x.Is_Deleted
             && x.Reminder_Date.Date <= today)
     .ToList();

由此生成的 MySQL 脚本如下：

WHERE (((`x.CRM_Note`.`Is_Deleted` = FALSE)
AND (`x`.`Is_Deleted` = FALSE))
AND (CONVERT(`x`.`Reminder_Date`, date) <= @__date_0))

这样我们就得到了一个正确的结果，澳洲客户也就收到了正确的消息。

是不是有种差之毫厘，谬以千里的感觉呢？