如何在LINQ查询中筛选子集合？现有查询异常求助

现有查询代码 #

var _cpResults = (await _context.QualityCaseResult
    .Where(q => q.QualityCaseId == model.QualityCaseId
        && (string.IsNullOrEmpty(model.ProductionNumber) || q.ProductionNumber.Contains(model.ProductionNumber)) 
        && (string.IsNullOrEmpty(model.OrderNumber) || q.OrderNumber.Contains(model.OrderNumber)))
    .Include(c => c.ControlPointResults)
        .ThenInclude(f => f.FormulaResults)
    .Include(c => c.ControlPointResults)
        .ThenInclude(i => i.Images.Where(x => x.IsUploaded))
    .Where(qcr => (qcr.ControlPointResults
        .Any(cpr => (cpr.ControlPointId == model.ControlPointId)
            && cpr.ProcessingDate > model.StartDate && qcr.ProcessingDate < model.EndDate
            && (string.IsNullOrEmpty(model.ResultRecipe) || cpr.ResultRecipe == Convert.ToBoolean(model.ResultRecipe)) // todo: convert to boolean in blazor app instead
            && (string.IsNullOrEmpty(model.PartNumber) || cpr.PartNumber.Contains(model.PartNumber))
            && (string.IsNullOrEmpty(model.ResultOperator) || cpr.ResultOperator.Contains(model.ResultOperator))))
    .Take(VVSConstants.SearchQualityCaseResultLimit)
    .AsNoTracking()
    .ToListAsync());

当前问题与需求 #

当前查询通过Any方法仅判断父实体QualityCaseResult下是否存在符合ControlPointId等条件的ControlPointResults子项，但实际需求是仅返回满足筛选条件的ControlPointResults子集合，而非父实体下的所有子项。

相关模型定义 #

请求模型 #

public class QualityCaseResultSearchParameters
{
    public string StationId { get; set; }
    public string QualityCaseId { get; set; }
    public string ControlPointId { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public string ProductionNumber { get; set; }
    public string OrderNumber { get; set; }
    public string PartNumber { get; set; }
    public string ResultRecipe { get; set; }
    public string ResultOperator { get; set; }
}

返回模型 #

public class QualityCaseResultModelForSearchResult : QualityCaseResultOverviewModel
{
    public QualityCaseResultModelForSearchResult() { }

    public QualityCaseResultModelForSearchResult(
        string id,
        DateTime processingDate,
        float processingTime,
        string productionNumber,
        string orderNumber,
        string status,
        string qualityCaseId,
        ICollection<ControlPointResultModelWithImagesAndFormulas> controlPointResults) : base(
            id: id,
            processingDate: processingDate,
            processingTime: processingTime,
            productionNumber: productionNumber,
            orderNumber: orderNumber,
            status: status,
            qualityCaseId: qualityCaseId)
    {
        ControlPointResults = controlPointResults;
    }
    public ICollection<ControlPointResultModelWithImagesAndFormulas> ControlPointResults { get; set; }
}

尝试的修改与异常 #

尝试在Include中添加Where子句筛选子集合，修改后的代码：

var _cpResults = await _context.QualityCaseResult
    .Where(q => q.QualityCaseId == model.QualityCaseId
       && (string.IsNullOrEmpty(model.ProductionNumber) || q.ProductionNumber.Contains(model.ProductionNumber))
       && (string.IsNullOrEmpty(model.OrderNumber) || q.OrderNumber.Contains(model.OrderNumber)))
    .Include(c => c.ControlPointResults
    .Where(pr => pr.ControlPointId == model.ControlPointId
      && c.ProcessingDate > model.StartDate
      && c.ProcessingDate < model.EndDate
      && (string.IsNullOrEmpty(model.ResultRecipe) || pr.ResultRecipe == Convert.ToBoolean(model.ResultRecipe))
      && (string.IsNullOrEmpty(model.PartNumber) || pr.PartNumber.Contains(model.PartNumber))
      && (string.IsNullOrEmpty(model.ResultOperator) || pr.ResultOperator.Contains(model.ResultOperator))))
   .ThenInclude(f => f.FormulaResults)
   .Include(c => c.ControlPointResults
   .Where(pr => pr.ControlPointId == model.ControlPointId
      && c.ProcessingDate > model.StartDate
      && c.ProcessingDate < model.EndDate
      && (string.IsNullOrEmpty(model.ResultRecipe) || pr.ResultRecipe == Convert.ToBoolean(model.ResultRecipe))
      && (string.IsNullOrEmpty(model.PartNumber) || pr.PartNumber.Contains(model.PartNumber))
      && (string.IsNullOrEmpty(model.ResultOperator) || pr.ResultOperator.Contains(model.ResultOperator))))
   .ThenInclude(i => i.Images.Where(x => x.IsUploaded))
   .Take(VVSConstants.SearchQualityCaseResultLimit)
   .AsNoTracking()
   .ToListAsync();

出现异常：

The LINQ expression 'c' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'。

刚接触LINQ，寻求正确解决方向。

解决方案 #

核心问题分析 #

异常原因是在Include的Where子句中直接引用了父实体变量c，EF Core无法将这种跨层级的变量引用翻译为SQL。同时重复编写相同筛选逻辑也造成了冗余。

正确实现方式 #

方式1：使用投影返回DTO（推荐）

直接通过Select投影到返回模型，在投影过程中精准筛选子集合，这是EF Core中处理关联数据筛选最可靠的方式：

// 提前处理ResultRecipe的布尔转换，避免在LINQ表达式中执行无法翻译的操作
bool? resultRecipe = null;
if (!string.IsNullOrEmpty(model.ResultRecipe))
{
    resultRecipe = Convert.ToBoolean(model.ResultRecipe);
}

var _cpResults = await _context.QualityCaseResult
    .Where(q => q.QualityCaseId == model.QualityCaseId
        && (string.IsNullOrEmpty(model.ProductionNumber) || q.ProductionNumber.Contains(model.ProductionNumber))
        && (string.IsNullOrEmpty(model.OrderNumber) || q.OrderNumber.Contains(model.OrderNumber))
        // 保留Any判断，确保只返回包含符合条件子项的父实体
        && q.ControlPointResults.Any(cpr => 
            cpr.ControlPointId == model.ControlPointId
            && cpr.ProcessingDate > model.StartDate 
            && q.ProcessingDate < model.EndDate
            && (resultRecipe == null || cpr.ResultRecipe == resultRecipe.Value)
            && (string.IsNullOrEmpty(model.PartNumber) || cpr.PartNumber.Contains(model.PartNumber))
            && (string.IsNullOrEmpty(model.ResultOperator) || cpr.ResultOperator.Contains(model.ResultOperator))))
    .Select(q => new QualityCaseResultModelForSearchResult(
        id: q.Id,
        processingDate: q.ProcessingDate,
        processingTime: q.ProcessingTime,
        productionNumber: q.ProductionNumber,
        orderNumber: q.OrderNumber,
        status: q.Status,
        qualityCaseId: q.QualityCaseId,
        controlPointResults: q.ControlPointResults
            .Where(cpr => 
                cpr.ControlPointId == model.ControlPointId
                && cpr.ProcessingDate > model.StartDate 
                && q.ProcessingDate < model.EndDate
                && (resultRecipe == null || cpr.ResultRecipe == resultRecipe.Value)
                && (string.IsNullOrEmpty(model.PartNumber) || cpr.PartNumber.Contains(model.PartNumber))
                && (string.IsNullOrEmpty(model.ResultOperator) || cpr.ResultOperator.Contains(model.ResultOperator)))
            .Select(cpr => new ControlPointResultModelWithImagesAndFormulas(
                // 根据你的模型构造参数填写对应字段
                id: cpr.Id,
                processingDate: cpr.ProcessingDate,
                resultRecipe: cpr.ResultRecipe,
                partNumber: cpr.PartNumber,
                resultOperator: cpr.ResultOperator,
                formulaResults: cpr.FormulaResults.ToList(),
                images: cpr.Images.Where(x => x.IsUploaded).ToList()
            )).ToList()
    ))
    .Take(VVSConstants.SearchQualityCaseResultLimit)
    .AsNoTracking()
    .ToListAsync();

方式2：使用EF Core筛选Include（EF Core 5+适用）

如果坚持使用Include，需要修正父实体属性的引用方式，通过子实体的导航属性访问父实体，同时避免重复筛选：

bool? resultRecipe = null;
if (!string.IsNullOrEmpty(model.ResultRecipe))
{
    resultRecipe = Convert.ToBoolean(model.ResultRecipe);
}

var _cpResults = await _context.QualityCaseResult
    .Where(q => q.QualityCaseId == model.QualityCaseId
        && (string.IsNullOrEmpty(model.ProductionNumber) || q.ProductionNumber.Contains(model.ProductionNumber))
        && (string.IsNullOrEmpty(model.OrderNumber) || q.OrderNumber.Contains(model.OrderNumber))
        && q.ControlPointResults.Any(cpr => 
            cpr.ControlPointId == model.ControlPointId
            && cpr.ProcessingDate > model.StartDate 
            && q.ProcessingDate < model.EndDate
            && (resultRecipe == null || cpr.ResultRecipe == resultRecipe.Value)
            && (string.IsNullOrEmpty(model.PartNumber) || cpr.PartNumber.Contains(model.PartNumber))
            && (string.IsNullOrEmpty(model.ResultOperator) || cpr.ResultOperator.Contains(model.ResultOperator))))
    // 仅写一次筛选Include，EF Core会自动应用到后续ThenInclude
    .Include(c => c.ControlPointResults
        .Where(pr => 
            pr.ControlPointId == model.ControlPointId
            && pr.ProcessingDate > model.StartDate 
            // 通过子实体的导航属性访问父实体属性
            && pr.QualityCaseResult.ProcessingDate < model.EndDate
            && (resultRecipe == null || pr.ResultRecipe == resultRecipe.Value)
            && (string.IsNullOrEmpty(model.PartNumber) || pr.PartNumber.Contains(model.PartNumber))
            && (string.IsNullOrEmpty(model.ResultOperator) || pr.ResultOperator.Contains(model.ResultOperator))))
    .ThenInclude(f => f.FormulaResults)
    .ThenInclude(i => i.Images.Where(x => x.IsUploaded))
    .Take(VVSConstants.SearchQualityCaseResultLimit)
    .AsNoTracking()
    .ToListAsync();

关键注意事项 #

1. 提前处理类型转换：将model.ResultRecipe的字符串转布尔值提前在业务层完成，避免LINQ表达式中出现无法翻译的转换操作。
2. 投影的优势：投影到DTO不仅能精准筛选子集合，还能减少数据库返回的数据量，提升查询性能。
3. 筛选Include的限制：EF Core的筛选Include不允许直接引用父实体变量，必须通过子实体的导航属性（如pr.QualityCaseResult）访问父实体属性。

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