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)))
哪个性能更好是不言而喻的。