如何在C# Entity Framework中实现简洁的异步SQL查询过滤？

结构优化与异步改造要点 #

• 用string.IsNullOrWhiteSpace()简化字符串空值判断逻辑
• 简化嵌套层级，让条件判断更直观清晰
• 遵循.NET异步规范，使用async/await实现非阻塞查询
• 调整方法命名与返回值，符合异步编码惯例

完整代码实现 #

using Microsoft.EntityFrameworkCore; // 需引入该命名空间使用EF Core异步Linq方法

private async Task<List<T_IFS_Inventory>> GetInventoryQueryAsync(T_IFS_InventorySelectDTO inventorySelection)
{
    var query = _db.T_IFS_Inventory.AsQueryable();

    // 优先按序列号过滤
    if (!string.IsNullOrWhiteSpace(inventorySelection.SerialNumber))
    {
        query = query.Where(q => q.SerialNumber == inventorySelection.SerialNumber);
    }
    else
    {
        // 扫描日期范围过滤
        if (inventorySelection.ScanDateStart != null)
        {
            query = query.Where(q => q.ScanDate >= inventorySelection.ScanDateStart);
            if (inventorySelection.ScanDateEnd != null)
                query = query.Where(q => q.ScanDate <= inventorySelection.ScanDateEnd);
        }

        // 状态日期范围过滤
        if (inventorySelection.StatusDateStart != null)
        {
            query = query.Where(q => q.DateStatus >= inventorySelection.StatusDateStart);
            if (inventorySelection.StatusDateEnd != null)
                query = query.Where(q => q.DateStatus <= inventorySelection.StatusDateEnd);
        }

        // 各类ID维度过滤
        if (inventorySelection.CategoryID != 0)
            query = query.Where(q => q.CategoryID == inventorySelection.CategoryID);
        if (inventorySelection.BrandID != 0)
            query = query.Where(q => q.BrandID == inventorySelection.BrandID);
        if (inventorySelection.ModelID != 0)
            query = query.Where(q => q.ModelID == inventorySelection.ModelID);
        if (inventorySelection.EmployeeID != 0)
            query = query.Where(q => q.EmployeeID == inventorySelection.EmployeeID);
        if (inventorySelection.StatusID != 0)
            query = query.Where(q => q.StatusID == inventorySelection.StatusID);
        if (inventorySelection.EmployeeStatusID != 0)
            query = query.Where(q => q.EmployeeStatusID == inventorySelection.EmployeeStatusID);
        if (inventorySelection.CurrentLocationID != 0)
            query = query.Where(q => q.CurrentLocationID == inventorySelection.CurrentLocationID);
    }

    // 异步执行数据库查询并返回结果
    return await query.ToListAsync();
}

核心细节说明 #

1. 异步实现：

   • 方法签名改为async Task<List<T_IFS_Inventory>>，后缀加Async符合.NET异步命名规范
   • 使用await query.ToListAsync()触发异步数据库查询，避免阻塞主线程
   • 确保项目已引入Microsoft.EntityFrameworkCore NuGet包，EF Core原生支持异步操作

2. 结构优化：

   • 用!string.IsNullOrWhiteSpace()替代原有的!= "" && != null双重判断，代码更简洁健壮
   • 变量名改为小驼峰格式（inventorySelection），符合C#编码规范
   • 保留原业务逻辑优先级（序列号优先过滤），未改动核心业务规则

3. 返回值选择：

   • 返回List<T_IFS_Inventory>而非IEnumerable，因为异步执行后查询已完成，返回具体集合更直观
   • 若需保留延迟执行灵活性，可返回Task<IQueryable<T_IFS_Inventory>>，但调用方需自行处理异步执行逻辑

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