C# LINQ Lambda表达式中DateTime列表匹配报错解决

我拥有一个DateTime列表：

List<DateTime> myDateTimeList = new List<DateTime>();

想要获取数据库中CreationDateTime字段（非Nullable的DateTime类型）存在于该列表中的所有记录。尝试了以下两种方法：

方法1 #

var result = myEntity.Where(x => myDateTimeList.Any(dt => dt == x.CreationDateTime));

方法2 #

var result = myEntity.Where(x => myDateTimeList.Contains(x.CreationDateTime));

调用await result.ToListAsync();时均抛出异常：

Conversion failed when converting date and/or time from character string.

这个错误多是EF Core将内存中DateTime列表转换为SQL查询时，出现日期精度不匹配或格式解析问题导致的，以下是几种可行的解决办法：

1. 统一日期精度，消除毫秒级差异 #

数据库的DateTime字段精度（比如SQL Server的datetime精度为3.33毫秒）通常低于.NET的DateTime，精度不一致会触发转换错误。可以先截断列表中日期的精度，和数据库对齐：

// 适配SQL Server datetime类型的精度，截断到3.33毫秒级
var truncatedDates = myDateTimeList.Select(dt => new DateTime(
    dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second,
    (int)Math.Round(dt.Millisecond / 3.333) * 3,
    dt.Kind
)).ToList();

var result = myEntity.Where(x => truncatedDates.Contains(x.CreationDateTime));

2. 使用表值参数（适合大数据量场景） #

如果日期列表条目较多，直接用Contains会生成过长的SQL语句，也容易触发转换问题。可以通过表值参数将列表传入数据库匹配：

步骤1：在SQL Server中创建表值类型 #

CREATE TYPE DateListType AS TABLE (DateValue DATETIME NOT NULL);

步骤2：在EF中调用表值参数查询 #

// 将DateTime列表转换为DataTable
var dateTable = new DataTable();
dateTable.Columns.Add("DateValue", typeof(DateTime));
foreach (var dt in myDateTimeList)
{
    dateTable.Rows.Add(dt);
}

// 执行SQL查询
var result = myEntity.FromSqlRaw(@"
    SELECT * FROM MyEntity 
    WHERE CreationDateTime IN (SELECT DateValue FROM @Dates)
", new SqlParameter("@Dates", SqlDbType.Structured)
{
    TypeName = "DateListType",
    Value = dateTable
}).ToListAsync();

3. 强制SQL端处理格式匹配 #

可以将日期转换为数据库可直接解析的字符串格式，再进行匹配：

// 用统一格式转换日期为字符串
var dateStrings = myDateTimeList.Select(dt => dt.ToString("yyyy-MM-dd HH:mm:ss.fff")).ToList();
var result = myEntity.Where(x => dateStrings.Contains(x.CreationDateTime.ToString("yyyy-MM-dd HH:mm:ss.fff")));

注意：这种方式可能无法利用CreationDateTime字段的索引，仅建议小数据量场景使用。

4. 升级EF Core及数据库提供程序 #

部分旧版本EF Core在处理DateTime列表的Contains时存在Bug，建议升级到最新稳定版的EF Core，同时保证数据库提供程序（如Microsoft.EntityFrameworkCore.SqlServer）版本与EF Core版本一致。

内容的提问来源于stack exchange，提问作者Steven Sann