问题

调用:

return await this._mainRepository.QueryListAsync(
    m => m.IsValid == 1
    && Convert.ToDateTime(sailingDates[0]) <= Convert.ToDateTime(m.SailingDate) && Convert.ToDateTime(m.SailingDate) <= Convert.ToDateTime(sailingDates[1])
);

报错： The LINQ expression 'DbSet()\r\n .Where(h => (int)h.IsValid == 1 && __ToDateTime_3 <= Convert.ToDateTime(h.SailingDate) && Convert.ToDateTime(h.SailingDate) <= __ToDateTime_4)' could not be translated. Additional information: Translation of method 'System.Convert.ToDateTime' failed. If this method can be mapped to your custom function, see go.microsoft.com/fwlink/?lin… for more information.\r\nTranslation of method 'System.Convert.ToDateTime' failed. If this method can be mapped to your custom function, see go.microsoft.com/fwlink/?lin… 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 go.microsoft.com/fwlink/?lin… for more information.

分析

EF Core中的LINQ查询最终都是被转换成SQL语句在数据库中执行，但并不是所有的C#方法都能被转换的。

解决方案

1. 调用映射的自定义函数 定义自定义函数

CREATE FUNCTION FN_ToDateTime
(
	@strDt nvarchar(50)
)
RETURNS datetime
AS
BEGIN
	RETURN cast(@strDt as datetime)
END

定义CLR方法

public DateTime ToDateTime(string strDt) => throw new NotSupportedException();

映射自定义函数

modelBuilder.HasDbFunction(typeof(DrWorksCPOEDbContext).GetMethod(nameof(ToDateTime), new[] { typeof(string) })).HasName("FN_ToDateTime");

LINQ查询中调用

return await this._mainRepository.QueryListAsync(
    m => m.IsValid == 1
    && _dbContext.ToDateTime(sailingDates[0]) <= _dbContext.ToDateTime(m.SailingDate) && _dbContext.ToDateTime(m.SailingDate) <= _dbContext.ToDateTime(sailingDates[1])
);

生成的sql

exec sp_executesql N'SELECT [h].[Id]
FROM [Main] AS [h]
WHERE ((((([h].[IsValid] = CAST(1 AS smallint)) AND ([dbo].[FN_ToDateTime](@__p_4) <= [dbo].[FN_ToDateTime]([h].[SailingDate]))) AND ([dbo].[FN_ToDateTime]([h].[SailingDate]) <= [dbo].[FN_ToDateTime](@__p_5))',N'@__p_4 nvarchar(4000),@__p_5 nvarchar(4000)',@__p_4=N'2023-2-12',@__p_5=N'2023-2-12'

2. 在C#客户端筛选

List<MainEntity> list = await this._mainRepository.QueryListAsync(
    m => m.IsValid == 1
);
return list.Where(m =>
    Convert.ToDateTime(sailingDates[0]) <= Convert.ToDateTime(m.SailingDate) && Convert.ToDateTime(m.SailingDate) <= Convert.ToDateTime(sailingDates[1])
);

参考

用户定义的函数映射 - EF Core | Microsoft Learn