如何对EF Core中.Include加载的子记录进行查询？

错误原因 #

原代码中筛选供应商的语句：

query = query.Where(o => o.Suppliers.Where(s => s.SupplierName == supplierName));

o.Suppliers.Where(...)返回的是IEnumerable<SupplierByOperation>集合，但Where方法要求传入返回布尔值的表达式，类型不匹配导致报错。

解决方案 #

根据需求选择以下两种方案之一：

方案1：仅筛选主表（保留包含指定供应商的Operation），加载所有供应商 #

将内部的Where替换为Any，Any会返回布尔值，表示集合中是否存在符合条件的元素：

if (!string.IsNullOrWhiteSpace(supplierName))
{
    query = query.Where(o => o.Suppliers.Any(s => s.SupplierName == supplierName));
}

方案2：同时筛选主表+仅加载符合条件的供应商（EF Core 5.0及以上版本支持） #

使用筛选式Include（Filtered Include），既能过滤主表中包含指定供应商的Operation，又能让Include只加载符合条件的供应商数据：

if (!string.IsNullOrWhiteSpace(supplierName))
{
    query = query
        .Include(x => x.Suppliers.Where(s => s.SupplierName == supplierName))
        .Where(o => o.Suppliers.Any(s => s.SupplierName == supplierName));
}

注意：单独使用Include(x => x.Suppliers.Where(...))只会筛选加载的子集合，不会过滤主表，因此需要配合Where(o => o.Suppliers.Any(...))实现主表和子表的双重筛选。

修改后的完整Repo代码 #

private IQueryable<Operation> GenerateBaseQuery(string location, string stockNumber = "", 
string exceptionType = "", string supplierName = "")
{
    var query = _dbContext.Operation.AsNoTracking()
        .Include(x => x.Suppliers)
        .Where(_ => _.Location.Equals(location));

    if (!string.IsNullOrWhiteSpace(stockNumber))
    {
        var convertedStockNumber = long.Parse(stockNumber);
        query = query.Where(e => e.StockNumber.Equals(convertedStockNumber));
    }
    
    if (!string.IsNullOrWhiteSpace(exceptionType))
    {
        query = exceptionType.ToLower() switch
        {
            "parts" => query.Where(e => e.HasPartLineItems == true),
            "sublets" => query.Where(e => e.HasSubletItems == true),
            "all" => query.Where(e => e.HasPartLineItems == true && e.HasSubletItems == true),
            _ => query
        };
    }
    
    if (!string.IsNullOrWhiteSpace(supplierName))
    {
        // 如需使用方案1，替换为方案1的代码即可
        query = query
            .Include(x => x.Suppliers.Where(s => s.SupplierName == supplierName))
            .Where(o => o.Suppliers.Any(s => s.SupplierName == supplierName));
    }

    return query;
}

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