C#中IQueryable多条件过滤异常：参数判断与日期逻辑问题求助

问题背景 #

在ASP.NET Core控制器中，通过IQueryable对数据库Computer表做多条件过滤（序列号、房间号、价格区间、安装日期区间）时遇到以下问题：

1. 初始使用值类型参数（decimal、DateTime）时，无用户输入仍会触发过滤分支
2. 改为全字符串参数后参数判断正常，但日期过滤逻辑错误——输入起始日期11/14/2019却显示11/15/2019的记录

原代码如下：

public async Task<IActionResult> Index(string searchSerial, string searchRoom, decimal searchPrice1, decimal searchPrice2, DateTime searchDate1, DateTime searchDate2)
{
    var computers = from m in _context.Computers
                    select m;

    
    if (!String.IsNullOrEmpty(searchSerial))
    {
        computers = computers.Where(s => s.ManufacturerSerialNumber.ToString()!.Equals(searchSerial));

    }

    if (!String.IsNullOrEmpty(searchRoom))
    {
        computers = computers.Where(s => s.OfficeRoomNumber!.Equals(searchRoom));

    }
    
    if (!String.IsNullOrEmpty(""+searchPrice1) || !String.IsNullOrEmpty(""+searchPrice2))
    {
        if (String.IsNullOrEmpty("" + searchPrice1))
        {
            computers = computers.Where(s => !(s.Price > searchPrice2));

        }
        else if (String.IsNullOrEmpty("" + searchPrice2))
        {
            computers = computers.Where(s => !(s.Price < searchPrice1));

        }
        else
        {
            computers = computers.Where(s => !(s.Price < searchPrice1));
            computers = computers.Where(s => !(s.Price > searchPrice2));

        }
    }
    
    if (!String.IsNullOrEmpty(""+searchDate1) || !String.IsNullOrEmpty("" + searchDate2))
    {
       
        if (String.IsNullOrEmpty("" + searchDate1))
        {
            computers = computers.Where(s => !(s.InstallationDate.CompareTo(searchDate2) < 0));

        }
        else if (String.IsNullOrEmpty("" + searchDate2))
        {
            computers = computers.Where(s => !(s.InstallationDate.CompareTo(searchDate1) > 0));

        }
        else
        {
            computers = computers.Where(s => !(s.InstallationDate.CompareTo(searchDate2) < 0));
            computers = computers.Where(s => !(s.InstallationDate.CompareTo(searchDate1) > 0));

        }
    }
    return View(await computers.ToListAsync());
}

修复方案 #

1. 修正参数传递逻辑 #

值类型（decimal、DateTime）会自动初始化默认值（如decimal默认0，DateTime默认0001/01/01），导致无输入时仍触发过滤。改用可空值类型接收参数，无输入时参数为null，判断更准确：

public async Task<IActionResult> Index(string? searchSerial, string? searchRoom, decimal? searchPrice1, decimal? searchPrice2, DateTime? searchDate1, DateTime? searchDate2)

2. 修复价格区间过滤 #

替换原有的字符串拼接判断方式，直接基于可空参数的HasValue属性判断：

if (searchPrice1.HasValue || searchPrice2.HasValue)
{
    if (!searchPrice1.HasValue)
    {
        // 仅限制最大价格：价格 <= 指定值
        computers = computers.Where(s => s.Price <= searchPrice2.Value);
    }
    else if (!searchPrice2.HasValue)
    {
        // 仅限制最小价格：价格 >= 指定值
        computers = computers.Where(s => s.Price >= searchPrice1.Value);
    }
    else
    {
        // 价格区间过滤：最小价格 <= 价格 <= 最大价格
        computers = computers.Where(s => s.Price >= searchPrice1.Value && s.Price <= searchPrice2.Value);
    }
}

3. 修复日期区间过滤 #

日期错误的核心是逻辑运算符完全颠倒，同时未处理DateTime的时间部分（数据库记录可能包含具体时间，用户输入仅为日期）：

if (searchDate1.HasValue || searchDate2.HasValue)
{
    // 处理时间部分：将用户输入日期转为当天零点/最后一刻
    DateTime? startDate = searchDate1.HasValue ? searchDate1.Value.Date : null;
    DateTime? endDate = searchDate2.HasValue ? searchDate2.Value.Date.AddDays(1).AddTicks(-1) : null;

    if (!startDate.HasValue)
    {
        // 仅限制结束日期：安装日期 <= 当天最后一刻
        computers = computers.Where(s => s.InstallationDate <= endDate.Value);
    }
    else if (!endDate.HasValue)
    {
        // 仅限制起始日期：安装日期 >= 当天零点
        computers = computers.Where(s => s.InstallationDate >= startDate.Value);
    }
    else
    {
        // 日期区间过滤：起始日期 <= 安装日期 <= 结束日期最后一刻
        computers = computers.Where(s => s.InstallationDate >= startDate.Value && s.InstallationDate <= endDate.Value);
    }
}

说明：AddDays(1).AddTicks(-1)将结束日期转为当天的最后一刻（如2019/11/14 23:59:59.9999999），确保包含当天所有带时间的记录。

4. 优化字符串匹配体验 #

将严格相等匹配改为忽略大小写，提升用户体验：

if (!string.IsNullOrEmpty(searchSerial))
{
    computers = computers.Where(s => s.ManufacturerSerialNumber.ToString().Equals(searchSerial, StringComparison.OrdinalIgnoreCase));
}

if (!string.IsNullOrEmpty(searchRoom))
{
    computers = computers.Where(s => s.OfficeRoomNumber.Equals(searchRoom, StringComparison.OrdinalIgnoreCase));
}

完整修复后代码 #

public async Task<IActionResult> Index(string? searchSerial, string? searchRoom, decimal? searchPrice1, decimal? searchPrice2, DateTime? searchDate1, DateTime? searchDate2)
{
    var computers = _context.Computers.AsQueryable();

    // 序列号过滤
    if (!string.IsNullOrEmpty(searchSerial))
    {
        computers = computers.Where(s => s.ManufacturerSerialNumber.ToString().Equals(searchSerial, StringComparison.OrdinalIgnoreCase));
    }

    // 房间号过滤
    if (!string.IsNullOrEmpty(searchRoom))
    {
        computers = computers.Where(s => s.OfficeRoomNumber.Equals(searchRoom, StringComparison.OrdinalIgnoreCase));
    }

    // 价格区间过滤
    if (searchPrice1.HasValue || searchPrice2.HasValue)
    {
        if (!searchPrice1.HasValue)
        {
            computers = computers.Where(s => s.Price <= searchPrice2.Value);
        }
        else if (!searchPrice2.HasValue)
        {
            computers = computers.Where(s => s.Price >= searchPrice1.Value);
        }
        else
        {
            computers = computers.Where(s => s.Price >= searchPrice1.Value && s.Price <= searchPrice2.Value);
        }
    }

    // 安装日期区间过滤
    if (searchDate1.HasValue || searchDate2.HasValue)
    {
        DateTime? startDate = searchDate1.HasValue ? searchDate1.Value.Date : null;
        DateTime? endDate = searchDate2.HasValue ? searchDate2.Value.Date.AddDays(1).AddTicks(-1) : null;

        if (!startDate.HasValue)
        {
            computers = computers.Where(s => s.InstallationDate <= endDate.Value);
        }
        else if (!endDate.HasValue)
        {
            computers = computers.Where(s => s.InstallationDate >= startDate.Value);
        }
        else
        {
            computers = computers.Where(s => s.InstallationDate >= startDate.Value && s.InstallationDate <= endDate.Value);
        }
    }

    return View(await computers.ToListAsync());
}

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