EF Core Linq to Entities多层级Where子句过滤方案问询

正在开发一个支持可选搜索参数的API端点，因数据结构复杂且受客户规则限制，只能使用Linq to Entities而非存储过程。当前需要解决的是在实体数据的不同层级（而非仅顶层）应用过滤条件，例如过滤Stream Id时，Where子句应放置在何处？考虑为Library、Version、Stream各层级分别创建PredicateBuilder，并插入对应位置的Where子句，但此前未尝试过该方式，寻求此类场景下的最优高效实现方案。

补充说明 #

原本计划为每个层级的过滤条件创建独立的Predicate，示例如下：

var p = PredicateBuilder.New<Library>();
if (request.LibraryId > 0) 
    p = p.And(x => x.LibraryId == request.LibraryId);
if (!string.IsNullOrEmpty(request.LibraryName)) 
    p = p.And(x => x.LibraryName!.Contains(request.LibraryName));

之后使用.Where(p)，但从未在多个层级中这样使用过。

示例查询 #

var libraries = DBContext.Library!.DefaultIfEmpty()!
    .Include(x => x.Version)!
    .ThenInclude(v => v.Emission)!
    .ThenInclude(e => e.StreamEmission)!
    .ThenInclude(se => se!.Stream)!
    //.WHERE(predicate1)** 
    //按Library Id、Library Name过滤
    .Select(lm => new LibraryViewModel
    {
        LibraryId = lm.LibraryId,
        LibraryName = lm.LibraryName,
        Versions = lm.Versions!
        //.WHERE(predicate2) 
        //按Version Id、Version Name、创建日期过滤
        .Select(vm => new VersionViewModel
        {
            VersionId = vm.VersionId,
            VersionName = vm.VersionName,
            CreateDate = vm.CreateDate,
            Streams = vm.Emission!
            .Select(vdse => vdse.StreamEmission)
            .Select(ds => ds!Stream)
            //.WHERE(predicate3)
            //按Stream Id或Stream Name过滤
            .GroupBy(dse => new { dse!.StreamId, dse.StreamName })
            .Select(sm => new StreamViewModel
            {
                StreamId = sm!.Key.StreamId,
                StreamName = sm.Key.StreamName,
            }).ToList()
        }).ToList()
    });

示例JSON响应 #

[{
  "LibraryId" : 24,
  "LibraryName" : "Library 24",
  "Versions" : [
  {
      "VersionId" : 30,
      "VersionName" : "Version 30",
      "CreateDate" : "12/1/2019",
      "Streams" : [
          {
              "StreamId" : 1,
              "StreamName" : "Stream Name 1"
          },
          {
              "StreamId" : 2,
              "StreamName" : "Stream Name 2"
          }]
  }]
}]

实体类定义 #

public class Library
{
 public int LibraryId {get;set;}
 public string LibraryName {get;set;}
 //导航属性
 public ICollection<Version> Versions {get;set;}
}

public class Version
{
 public int VersionId {get;set;}
 public string VersionName {get;set;}
 public DateTime CreateDate {get;set;}
 //导航属性
 public ICollection<Emission> Emissions {get;set;}
 //导航属性
 public Library? Library {get;set;}
}

//Version与StreamEmission的关联类
public class Emission
{
 public int EmissionId {get;set;}
 public string Name {get;set;}
 //外键
 public int StreamEmissionId {get;set;}
 //导航属性
 public StreamEmission StreamEmission {get;set;}
 public int VersionId {get;set;}
 //导航属性
 public Version? Version {get;set;}
}

//Stream与Emission的关联类
public class StreamEmission 
{
 public int StreamEmissionId {get;set;}
 public int StreamId {get;set;}
 //导航属性
 public Stream Stream {get;set;}
}

public class Stream
{
 public int StreamId {get;set;}
 public string StreamName {get;set;}
 //导航属性
 public ICollection<StreamEmission> StreamEmissions {get;set;}
}

1. 分层级构建独立Predicate #

为每个层级（Library、Version、Stream）分别创建对应的Predicate，保持过滤逻辑的独立性和可维护性：

// Library层级Predicate
var libraryPredicate = PredicateBuilder.New<Library>();
if (request.LibraryId > 0)
    libraryPredicate = libraryPredicate.And(l => l.LibraryId == request.LibraryId);
if (!string.IsNullOrEmpty(request.LibraryName))
    libraryPredicate = libraryPredicate.And(l => l.LibraryName.Contains(request.LibraryName));

// Version层级Predicate
var versionPredicate = PredicateBuilder.New<Version>();
if (request.VersionId > 0)
    versionPredicate = versionPredicate.And(v => v.VersionId == request.VersionId);
if (!string.IsNullOrEmpty(request.VersionName))
    versionPredicate = versionPredicate.And(v => v.VersionName.Contains(request.VersionName));
if (request.StartDate.HasValue)
    versionPredicate = versionPredicate.And(v => v.CreateDate >= request.StartDate.Value);
if (request.EndDate.HasValue)
    versionPredicate = versionPredicate.And(v => v.CreateDate <= request.EndDate.Value);

// Stream层级Predicate
var streamPredicate = PredicateBuilder.New<Stream>();
if (request.StreamId > 0)
    streamPredicate = streamPredicate.And(s => s.StreamId == request.StreamId);
if (!string.IsNullOrEmpty(request.StreamName))
    streamPredicate = streamPredicate.And(s => s.StreamName.Contains(request.StreamName));

2. 在对应层级的查询中应用Predicate #

Library层级：直接在顶层查询后追加Where

var query = DBContext.Library.AsQueryable();
if (libraryPredicate.Body.NodeType != ExpressionType.Constant) // 避免无过滤条件时添加多余Where
    query = query.Where(libraryPredicate);

Version层级：在Select内的Versions集合上应用Where

直接在lm.Versions后追加.Where(versionPredicate)即可，Linq to Entities会将其转换为对应的SQL关联过滤：

Versions = lm.Versions!
    .Where(versionPredicate)
    .Select(vm => new VersionViewModel { ... })
    .ToList()

Stream层级：在Stream集合的查询路径中插入Where

由于Stream是通过Emission -> StreamEmission关联的，需要在获取Stream的环节添加过滤：

Streams = vm.Emissions!
    .Select(vdse => vdse.StreamEmission.Stream)
    .Where(streamPredicate)
    .GroupBy(dse => new { dse.StreamId, dse.StreamName })
    .Select(sm => new StreamViewModel { ... })
    .ToList()

3. 注意Include与过滤的关系 #

如果只需要返回ViewModel中的数据，可以去掉Include——Linq to Entities在Select投影时会自动按需加载所需的关联数据，不需要显式Include，这样能避免加载多余数据，提升性能。

4. 性能优化要点 #

• 避免客户端评估：确保所有Predicate和过滤逻辑都能被Linq to Entities转换为SQL，不要在查询中使用无法转换的客户端方法。
• 索引优化：为过滤字段（LibraryId、VersionId、StreamId、各类Name字段、CreateDate）创建数据库索引，加速查询。
• 空值判断：仅当搜索参数有值时才添加对应的过滤条件，避免生成多余的SQL条件。
• 使用AsQueryable：确保所有集合操作都基于IQueryable而非IEnumerable，让过滤逻辑在数据库端执行。

完整优化后查询示例 #

// 构建各层级Predicate
var libraryPredicate = PredicateBuilder.New<Library>();
if (request.LibraryId > 0)
    libraryPredicate = libraryPredicate.And(l => l.LibraryId == request.LibraryId);
if (!string.IsNullOrEmpty(request.LibraryName))
    libraryPredicate = libraryPredicate.And(l => l.LibraryName.Contains(request.LibraryName));

var versionPredicate = PredicateBuilder.New<Version>();
if (request.VersionId > 0)
    versionPredicate = versionPredicate.And(v => v.VersionId == request.VersionId);
if (!string.IsNullOrEmpty(request.VersionName))
    versionPredicate = versionPredicate.And(v => v.VersionName.Contains(request.VersionName));
if (request.StartDate.HasValue)
    versionPredicate = versionPredicate.And(v => v.CreateDate >= request.StartDate.Value);
if (request.EndDate.HasValue)
    versionPredicate = versionPredicate.And(v => v.CreateDate <= request.EndDate.Value);

var streamPredicate = PredicateBuilder.New<Stream>();
if (request.StreamId > 0)
    streamPredicate = streamPredicate.And(s => s.StreamId == request.StreamId);
if (!string.IsNullOrEmpty(request.StreamName))
    streamPredicate = streamPredicate.And(s => s.StreamName.Contains(request.StreamName));

// 执行查询
var libraries = DBContext.Library.AsQueryable()
    .Where(libraryPredicate)
    .Select(lm => new LibraryViewModel
    {
        LibraryId = lm.LibraryId,
        LibraryName = lm.LibraryName,
        Versions = lm.Versions!
            .Where(versionPredicate)
            .Select(vm => new VersionViewModel
            {
                VersionId = vm.VersionId,
                VersionName = vm.VersionName,
                CreateDate = vm.CreateDate,
                Streams = vm.Emissions!
                    .Select(vdse => vdse.StreamEmission.Stream)
                    .Where(streamPredicate)
                    .GroupBy(dse => new { dse.StreamId, dse.StreamName })
                    .Select(sm => new StreamViewModel
                    {
                        StreamId = sm.Key.StreamId,
                        StreamName = sm.Key.StreamName,
                    }).ToList()
            }).ToList()
    })
    .ToList();

内容的提问来源于stack exchange，提问作者joby-flick