EF Core6.0中的函数映射

EF Core中的LINQ查询最终都是被转换成SQL语句在数据库中执行，但并不是所有的C#方法都能被转换的。一个典型的例子就是DateTime中的ToString(string? format)方法。
比如在ASP.NET的MVC教程项目MvcMovie中，我们想要查询今天上映的电影，因为数据库的上映日期ReleaseDate是包含时间的，在比较时我们只比较日期而忽略时间，写出来的LINQ查询如下：

var movies = context.Movies.Where(m => m.ReleaseDate.ToString("yyyy-MM-dd") == DateTime.Today.ToString("yyyy-MM-dd"));

这会报一个运行时错误：

An unhandled exception occurred while processing the request.
InvalidOperationException: The LINQ expression 'DbSet<Movie>()
.Where(m => m.ReleaseDate.ToString("yyyy-MM-dd") == DateTime.Today.ToString("yyyy-MM-dd"))' could not be translated. Additional information: Translation of method 'System.DateTime.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'System.DateTime.ToString' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, ref <>c__DisplayClass15_0 )

意思是“翻译System.DateTime.ToString方法失败”。后面还给出了解决方法。
第一就是自定义函数映射，也是本文主题，这个我们后面讲。
第二是重写一个能被翻译成SQL语句的查询。嗯……这个也在后面讲。
第三是通过调用’AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or 'ToListAsync’这几个方法切换到客户端再查询，比如：

var movies = context.Movies.ToList().Where(m => m.ReleaseDate.ToString("yyyy-MM-dd") == DateTime.Today.ToString("yyyy-MM-dd"));

这行代码先把context.Movies转换成List再调用Where方法查询，执行时没有任何问题。
但是这个执行过程是先从数据表获取所有记录，再从这些记录中筛选符合条件的记录。写成SQL语句就是如下（假设今天是2022年8月30日）：

SELECT * FROM (SELECT * FROM Movie) A WHERE A.ReleaseDate='2022-08-30'

而我们想要的SQL语句是：

SELECT * FROM Movie WHERE ReleaseDate='2022-08-30'

虽然结果一样，但执行性能差别很大。而且这里只是做个简单的比较，实际上在C#中这两种方式的性能差异比执行这两条SQL语句更大。
因此这个方法不考虑。
最后就是第一种方法：自定义函数映射。
首先我们在数据库写一个格式化日期数据的函数，下面是MySql中的函数：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `Movie`.`ToDateString`(d DATETIME)
    RETURNS VARCHAR(20)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	RETURN DATE_FORMAT(d, '%Y-%m-%d');
    END$$

DELIMITER ;

接下来在EF Core的数据上下文中映射一个C#的方法，完整代码如下：

//EF Core 数据上下文
public class DataContext : DbContext
{
    public DataContext(DbContextOptions<DataContext> options)
        : base(options)
    {
    }

    public DbSet<Movie> Movies { get; set; } = null!;

    //自定义方法
    public string ToDateString(DateTime date) => throw new NotSupportedException();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        //将数据库的函数映射到自定义方法
        modelBuilder.HasDbFunction(typeof(DataContext).GetMethod(nameof(ToDateString), new[] {typeof(DateTime)})!).HasName("ToDateString");
    }
}

控制器中：

//LINQ仍然不能翻译ToString方法，所以先得到当天日期的格式化字符串
var today = DateTime.Today.ToString("yyyy-MM-dd");
var movies = context.Movies.Where(m => context.ToDateString(m.ReleaseDate) == today);
return View(await movies.ToListAsync());

可以看一下EF Core生成的SQL语句：

SELECT `m`.`Id`, `m`.`Genre`, `m`.`Price`, `m`.`ReleaseDate`, `m`.`Title`
FROM `Movie` AS `m`
WHERE `ToDateString`(`m`.`ReleaseDate`) = '2022-08-30'

完全达到我们的要求。
好像有点麻烦，我们看看第二种方法，似乎也可以单纯用C#的代码来实现日期比较，不用搞什么函数映射，比如这样：

var movies = context.Movies.Where(m => m.ReleaseDate.Year == DateTime.Today.Year && m.ReleaseDate.Month == DateTime.Today.Month && m.ReleaseDate.Day == DateTime.Today.Day);

同样实现了只比较日期，为什么不这么做？来看看上面这行代码生成的SQL语句：

SELECT `m`.`Id`, `m`.`Genre`, `m`.`Price`, `m`.`ReleaseDate`, `m`.`Title`
FROM `Movie` AS `m`
WHERE (((EXTRACT(year FROM `m`.`ReleaseDate`) = EXTRACT(year FROM CURDATE())) OR (EXTRACT(year FROM `m`.`ReleaseDate`) IS NULL AND (EXTRACT(year FROM CURDATE()) IS NULL))) AND ((EXTRACT(month FROM `m`.`ReleaseDate`) = EXTRACT(month FROM CURDATE())) OR (EXTRACT(month FROM `m`.`ReleaseDate`) IS NULL AND (EXTRACT(month FROM CURDATE()) IS NULL)))) AND ((EXTRACT(day FROM `m`.`ReleaseDate`) = EXTRACT(day FROM CURDATE())) OR (EXTRACT(day FROM `m`.`ReleaseDate`) IS NULL AND (EXTRACT(day FROM CURDATE()) IS NULL)))

哪个性能更好是不言而喻的。