Asp.Net Core中MSSQL datetime2字段多语言日期搜索适配问题

问题根源 #

你的代码中x.CreateDate.ToString()会被EF Core翻译成SQL默认的日期格式（如yyyy-MM-dd），但客户端显示的是本地化格式（如MM/dd/yyyy），导致搜索时格式不匹配。同时EF Core无法直接翻译ToShortDateString()这类带格式的日期转换方法，直接客户端处理又会导致大数据量下的性能问题。

可行解决方案 #

1. 解析搜索输入为日期范围查询（推荐）

避开字符串格式依赖，直接将用户输入的日期片段解析为日期范围，用日期字段做范围匹配，性能最优且不依赖格式：

DateTime? startDate = null;
DateTime? endDate = null;
// 适配多语言环境下的常见日期片段格式
var possibleFormats = new[] { "MM/dd", "dd/MM", "yyyy-MM", "MM/yyyy", "yyyy" };
foreach (var format in possibleFormats)
{
    if (DateTime.TryParseExact(searchValue, format, CultureInfo.CurrentCulture, DateTimeStyles.None, out DateTime parsedDate))
    {
        switch (format.Length)
        {
            case 5: // 匹配MM/dd或dd/MM格式
                startDate = new DateTime(DateTime.Now.Year, parsedDate.Month, parsedDate.Day);
                endDate = startDate.Value.AddDays(1);
                break;
            case 7: // 匹配yyyy-MM或MM/yyyy格式
                startDate = new DateTime(parsedDate.Year, parsedDate.Month, 1);
                endDate = startDate.Value.AddMonths(1);
                break;
            case 4: // 匹配yyyy格式
                startDate = new DateTime(parsedDate.Year, 1, 1);
                endDate = startDate.Value.AddYears(1);
                break;
        }
        break;
    }
}

IQueryable<Entity> query = Entities;
// 添加日期范围筛选
if (startDate.HasValue && endDate.HasValue)
{
    query = query.Where(x => x.CreateDate >= startDate.Value && x.CreateDate < endDate.Value);
}
// 保留对文本部分的搜索（如"created by admin"）
if (!string.IsNullOrEmpty(searchValue) && !startDate.HasValue)
{
    query = query.Where(x => (localizedText?.ToLower().Contains(searchValue.ToLower()) ?? false));
}

2. 在SQL层面生成本地化格式字符串

利用MSSQL的FORMAT函数，在数据库端将日期转换为客户端格式，再进行字符串匹配：

// 从当前请求的文化信息中获取短日期格式
var dateFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
var searchLower = searchValue.ToLower();
var localizedTextLower = localizedText?.ToLower() ?? string.Empty;

// 使用参数化查询避免SQL注入
IQueryable<Entity> query = Entities.FromSqlRaw(@"
    SELECT * FROM Entities 
    WHERE LOWER(FORMAT(CreateDate, {0}) + ' ' + {1}) LIKE '%' + {2} + '%'
", dateFormat, localizedTextLower, searchLower);

注意：FORMAT函数可能存在性能损耗，数据量极大时需谨慎使用。

3. 预计算本地化日期字段（大数据量场景）

在数据库中添加持久化计算列，预存格式化后的日期文本，直接对该列进行搜索：

• 先在MSSQL中创建计算列：

ALTER TABLE Entities 
ADD FormattedCreateDate AS LOWER(FORMAT(CreateDate, 'MM/dd/yyyy') + ' created by admin') PERSISTED;

• 在EF Core实体类中映射该字段后，直接查询：

query = query.Where(x => x.FormattedCreateDate.Contains(searchValue.ToLower()));

优点：查询性能高；缺点：增加存储成本，若本地化格式变更需同步修改计算列定义。

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