c# - 查询需要在 .ThenInclude 之后使用 .Where 子句进行过滤

Question

我使用以下查询来过滤我想要的 BranchId。

var query = await _dbContext.TargetItems                
.Include(i => i.BranchTargetItem)
.ThenInclude(i => i.BranchTarget)                
.Where(x=> x.BranchTargetItem.Any(x=> x.BranchTarget.Any(x=> x.BranchId.Equals("9417"))))
.ToListAsync(cancellationToken);

上面的表达式创建以下 SQL，我认为.Any()导致这些Left Join语句

SELECT *
  FROM PERF_TARGET_ITEMS "p"
  LEFT JOIN (SELECT *
               FROM PERF_BRANCH_TARGET_ITEMS "p0"
               LEFT JOIN PERF_BRANCH_TARGETS "p1"
                 ON "p0".BTI_ID = "p1".BT_BRANCH_TARGET_ITEM_ID) "t"
    ON "p".TI_ID = "t".BTI_ITEM_TARGET_ITEM_ID
 WHERE EXISTS (SELECT 1
          FROM PERF_BRANCH_TARGET_ITEMS "p2"
         WHERE ("p".TI_ID = "p2".BTI_ITEM_TARGET_ITEM_ID)
           AND EXISTS
         (SELECT 1
                  FROM PERF_BRANCH_TARGETS "p3"
                 WHERE ("p2".BTI_ID = "p3".BT_BRANCH_TARGET_ITEM_ID)
                   AND ("p3".BT_BRANCH_ID = N'9417')))
 ORDER BY "p".TI_ID, "t".BTI_ID, "t".BT_ID

但我想转换Left Join为Inner Join如下，

  SELECT *
    FROM WESTCORE.PERF_BRANCH_TARGET_ITEMS "p"
   INNER JOIN WESTCORE.PERF_PERIODS "p0"
      ON "p".BTI_ITEM_PERIOD_ID = "p0".P_ID
   INNER JOIN WESTCORE.PERF_TARGET_ITEMS "p1"
      ON "p".BTI_ITEM_TARGET_ITEM_ID = "p1".TI_ID
    LEFT JOIN (SELECT *
                 FROM WESTCORE.PERF_BRANCH_TARGETS "p2"
                WHERE "p2".BT_BRANCH_ID = '9417') "t"
      ON "p".BTI_ID = "t".BT_BRANCH_TARGET_ITEM_ID
   WHERE ((SELECT COUNT(*)
             FROM WESTCORE.PERF_BRANCH_TARGETS "p3"
            WHERE ("p".BTI_ID = "p3".BT_BRANCH_TARGET_ITEM_ID)
              AND ("p3".BT_BRANCH_ID = '9417')) > 0)
   ORDER BY "p".BTI_ID, "p0".P_ID, "p1".TI_ID, "t".BT_ID

我想返回TargetItems包含它的关系实体（TargetItems > BranchTargetItems > BranchTargets）

//TargetItem.cs
    ...
    public virtual ICollection<BranchTargetItem> BranchTargetItem { get; set; }
    ...

//BranchTargetItem.cs
    ...
    public virtual ICollection<BranchTarget> BranchTarget { get; set; }
    ...

//BranchTarget.cs
    ...
    public virtual BranchTargetItem BranchTargetItem { get; set; }
    ...

这是我的人际关系，

//TargetItemConfiguration.cs

    builder.HasMany(x => x.BranchTargetItem).WithOne(x => x.TargetItem).HasForeignKey(x => x.ItemTargetItemId).IsRequired();

//BranchTargetItemConfiguration.cs
    builder.HasMany(x => x.BranchTarget).WithOne(x => x.BranchTargetItem).HasForeignKey(x => x.BranchTargetItemId).IsRequired();

编辑：

我只想将此过滤器添加.ThenInclude( bti=> bti.BranchTarget).Where( bt=> bt.BranchId.Equals("9417"))到以下查询中，

    var query = await _dbContext.TargetItems
        .Include(ti => ti.BranchTargetItem)
        .ThenInclude( bti=> bti.BranchTarget)
        .ToListAsync(cancellationToken);

编辑：

我已经使用以下查询解决了这个问题，但我仍在寻找理想的解决方案。

    var targetItems = await _dbContext.TargetItems
        .Include(ti => ti.BranchTargetItem)
        .ThenInclude(bti => bti.BranchTarget)
        .ToListAsync(cancellationToken);

    foreach (TargetItem ti in targetItems)
    {
        foreach (BranchTargetItem bti in ti.BranchTargetItem)
        {
            bti.BranchTarget = bti.BranchTarget.Where(bt => bt.BranchId.Equals(branchId)).ToList();
        }
    }

编辑：

我也尝试过这种方法，TargetItems 实体在这里与其自身具有一对多的关系，[DataMember] public virtual ICollection<TargetItem> TargetItems { get; set; }当我应用下面的代码时，子节点为空。它只返回第一个节点。

    var targetItems = await _dbContext.BranchTargets
                    .Where(x => x.BranchId.Equals("9417"))
                    .Include(t => t.BranchTargetItem)
                    .ThenInclude(t => t.TargetItem)
                    .Select(x=> x.BranchTargetItem.TargetItem)
                    .ToListAsync(cancellationToken);

标签： c#entity-framework.net-coreentity-framework-core

Answer 1

这不是Any你应该担心的。Any-> EXISTS。你的问题是Include，它转化为LEFT OUTER JOIN. 所以你需要额外的过滤，像这样：

var query = await _dbContext.TargetItems
.Include(i => i.BranchTargetItem)
.Where(e => e.BranchTargetItem != null) //added filtration
.ThenInclude(i => i.BranchTarget)
.Where(e => e.BranchTarget != null) //added filtration
.Where(x => x.BranchTargetItem.Any(x => x.BranchTarget.Any(x => x.BranchId.Equals("9417"))))
.ToListAsync(cancellationToken);