使用PredicateBuilder按日期范围过滤时遇smalldatetime转换错误

问题原因 #

你遇到的smalldatetime转换错误，本质是UI层将日期转成字符串拼入谓词后，Dynamic Linq解析时把它识别为字符串类型，和SQL的smalldatetime字段比较时发生类型不匹配，导致转换失败。非API调用正常是因为直接在本地生成强类型表达式，不存在字符串转日期的问题。

解决方案 #

方案一：快速修复字符串谓词的日期拼接 #

如果坚持使用字符串谓词的方式，修改UI层的日期拼接逻辑，让Dynamic Linq能解析出正确的DateTime类型：

// UI层Index方法修改日期拼接部分
public async Task<IActionResult> Index(string? Date1 = "", string? Date2 = "")
{
    string predicateString = string.Empty;
    if (!string.IsNullOrEmpty(SearchText))
    {
        predicateString = $"NameSurname.Contains(\"{SearchText}\")";
    }
    
    DateTime? parsedDate1 = !string.IsNullOrEmpty(Date1) ? DateTime.Parse(Date1) : (DateTime?)null;
    DateTime? parsedDate2 = !string.IsNullOrEmpty(Date2) ? DateTime.Parse(Date2) : (DateTime?)null;
    if (parsedDate1.HasValue && parsedDate2.HasValue)
    {
        // 生成new DateTime(year, month, day)格式的表达式，让Dynamic Linq解析为日期类型
        var datePart1 = $"new DateTime({parsedDate1.Value.Year}, {parsedDate1.Value.Month}, {parsedDate1.Value.Day})";
        var datePart2 = $"new DateTime({parsedDate2.Value.Year}, {parsedDate2.Value.Month}, {parsedDate2.Value.Day})";
        predicateString += (string.IsNullOrEmpty(predicateString) ? "" : " && ") + $"UserStartDate >= {datePart1} && UserStartDate <= {datePart2}";
    }
    var apiService = _apiServiceFactory.Create<Member, JObject>();
    var dataObject = await apiService.GetDataPaged(pageRequest);
}

方案二：改用强类型筛选（推荐，更安全可靠） #

彻底避免字符串拼接的类型问题，改用强类型参数传递筛选条件：

1. 修改PageRequestDto：新增日期和搜索字段

public class PageRequestDto
{
    public int PageIndex { get; set; }
    public int Take { get; set; }
    public string OrderBy { get; set; }
    // 新增筛选字段
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string SearchText { get; set; }
}

2. UI层传递强类型参数：不再拼接谓词字符串

public async Task<IActionResult> Index(string? Date1 = "", string? Date2 = "")
{
    var pageRequest = new PageRequestDto
    {
        PageIndex = 1,
        Take = 25,
        SearchText = SearchText,
        StartDate = !string.IsNullOrEmpty(Date1) ? DateTime.Parse(Date1) : (DateTime?)null,
        EndDate = !string.IsNullOrEmpty(Date2) ? DateTime.Parse(Date2) : (DateTime?)null,
        OrderBy = "UserStartDate" // 默认升序，可根据UI选择改为"UserStartDate descending"
    };
    
    var apiService = _apiServiceFactory.Create<Member, JObject>();
    var dataObject = await apiService.GetDataPaged(pageRequest);
}

3. API层组合强类型谓词：用PredicateBuilder构建表达式

[HttpPost("GetDataPaged")]
public async Task<IActionResult> GetDataPagedAsync([FromBody] PageRequestDto pageRequest)
{
    try
    {
        var predicate = PredicateBuilder.New<Member>(true);

        // 处理姓名搜索
        if (!string.IsNullOrEmpty(pageRequest.SearchText))
        {
            predicate = predicate.And(m => m.NameSurname.Contains(pageRequest.SearchText));
        }

        // 处理日期范围筛选
        if (pageRequest.StartDate.HasValue && pageRequest.EndDate.HasValue)
        {
            predicate = predicate.And(m => m.UserStartDate >= pageRequest.StartDate.Value && m.UserStartDate <= pageRequest.EndDate.Value);
        }

        var result = await _serviceManager.Members.GetDataPagedAsync(predicate, pageRequest.PageIndex, pageRequest.Take, pageRequest.OrderBy);
        // 后续分页包装逻辑不变...
    }
    catch (Exception ex)
    {
        return CreateActionResultInstance(CustomResponseDto<string>.Fail(ex.Message, 400));
    }
}

实现UserStartDate排序 #

在Core层的GetDataPagedAsync方法中添加排序逻辑，利用Dynamic Linq的OrderBy扩展方法：

public async Task<PaginatedList<TEntity>> GetDataPagedAsync(Expression<Func<TEntity, bool>> predicate, int pageIndex, int take, string orderBy)
{
    var query = context.Set<TEntity>().AsQueryable();

    if (predicate != null)
    {
        query = query.Where(predicate);
    }

    // 处理排序：支持"UserStartDate"（升序）或"UserStartDate descending"（降序）
    if (!string.IsNullOrEmpty(orderBy))
    {
        query = query.OrderBy(orderBy);
    }

    var count = await query.CountAsync();
    var items = await query.Skip((pageIndex - 1) * take).Take(take).ToListAsync();
    
    return new PaginatedList<TEntity>(items, count, pageIndex, take);
}

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