如何筛选此LINQ查询中子集合

kfgdxczn  于 2022-12-06  发布在  其他
关注(0)|答案(1)|浏览(131)

我在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());

以下是请求模型:

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; }
}

查询“几乎”可以正常工作,除了下面这一行:

.Where(qcr => (qcr.ControlPointResults
                .Any(cpr => (cpr.ControlPointId == model.ControlPointId)

Any仅检查是否满足条件,但我需要的是基于ControlPointId获取数据。查询返回父QualityCaseResult下的所有ControlPint
以下是返回模型:

public class QualityCaseResultModelForSearchResult : QualityCaseResultOverviewModel

{搜索结果的公共质量案例结果模型(){ }

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; }

}
我对LINQ相当陌生,希望能在正确的方向上推动我
出现此错误的当前查询状态:
无法转换LINQ表达式“c”。请以可以转换的形式重写查询,或者通过插入对“AsEnumerable”、"AsAsyncEnumerable“、”ToList“或”ToListAsync“的调用来显式切换到客户端计算

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();
iyr7buue

iyr7buue1#

这听起来像是您在询问有关过滤结果相关数据的问题。EF Core中支持此功能,因此您有两个选项。过滤相关数据或在投影期间过滤数据。(在此处填充结果对象)
筛选相关实体:

.Include(c => c.ControlPointResults.Where(pr.ControlPointId == model.ControlPointId)
    && cpr.ProcessingDate > model.StartDate 
    && qcr.ProcessingDate < model.EndDate
    && (string.IsNullOrEmpty(model.ResultRecipe) 
        || cpr.ResultRecipe == Convert.ToBoolean(model.ResultRecipe)) 
    && (string.IsNullOrEmpty(model.PartNumber) 
        || cpr.PartNumber.Contains(model.PartNumber))
    && (string.IsNullOrEmpty(model.ResultOperator) 
        || cpr.ResultOperator.Contains(model.ResultOperator))))
    .ThenInclude(f => f.FormulaResults)
.Include(c => c.ControlPointResults.Where(pr.ControlPointId == model.ControlPointId)
    && cpr.ProcessingDate > model.StartDate 
    && qcr.ProcessingDate < model.EndDate
    && (string.IsNullOrEmpty(model.ResultRecipe) 
        || cpr.ResultRecipe == Convert.ToBoolean(model.ResultRecipe)) 
    && (string.IsNullOrEmpty(model.PartNumber) 
        || cpr.PartNumber.Contains(model.PartNumber))
    && (string.IsNullOrEmpty(model.ResultOperator) 
        || cpr.ResultOperator.Contains(model.ResultOperator))))
    .ThenInclude(i => i.Images.Where(x => x.IsUploaded))

我不确定是否需要在两个Incude上都使用Where条件,以便实现多个ThenInclude条件。(我的括号可能没有对齐)在您比较ControlPointResults中的开始日期的查询中,似乎也存在打字错误/错误(cpr.ProcessingDate〉模型.开始日期)但随后针对外部QualityCaseResult返回结束日期.(qcr.ProcessingDate〈模型.开始日期)
但是,这应该只包括匹配ControlPointId的ControlPointResults。
我不建议返回实体或类扩展实体作为视图的模型。主要原因是你经常加载和发送比你实际需要的数据多得多的数据。如果你只需要一个特定相关实体的一两个字段,最好让EF只获取这一两个属性,而不是为了提供它们而急切地加载整个实体图。
通过独立的视图模型和投影,另一个选项是在投影时筛选数据。这可以使用Select或Automapper的ProjectTo,其中Automapper配置为筛选相关子数据。通常最好避免在Linq表达式中嵌入条件检查。这意味着检查model.Value是否在Where/Any子句,而不是在子句外部使用if,以便仅在条件存在时附加筛选器。但是,在组合涉及内部函数(如Any)的更复杂表达式时,这可能需要构建一些非常复杂的表达式。
Projection的优点是它避免了加载关于实体图的所有内容,而视图可能只需要字段的子集。这可以过滤掉相关的实体,以及通常不加载关于相关实体的所有内容,而视图可能只需要几个字段。

相关问题