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时间。因此我们大概能知道，为什么澳洲客户会遇到上面的场景了。

PS： 根据7楼兄弟的反馈，我试了一下，改动Mysql的时区配置之后，果然CURRENT_TIMESTAMP()就改为了对应时区的时间。这里使用UTC时间的原因应该是我在AWS RDS上创建Mysql实例的时候，忽略了时区配置。

由于澳洲处于东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)) 

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

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