EF Core 8中Where IN OPENJSON查询过慢问题求助

问题分析 #

你当前的写法先通过存储过程获取GUID列表并执行ToListAsync()，后续用Contains时EF Core会将列表序列化为JSON，通过OPENJSON解析后做IN匹配。这种方式在数据量较大时容易导致索引失效或执行计划低效，而手动写IN集合能直接命中索引，因此性能差异显著。

可行的IQueryable方案 #

1. 将存储过程调用整合为查询的一部分（推荐） #

不要提前将存储过程结果转为List，而是保持为IQueryable<Guid>，让EF Core将存储过程调用作为子查询整合到主SQL中，避免客户端评估和OPENJSON的使用：

// 保持存储过程结果为IQueryable，不提前ToList
var relatedWorkflowIdsQuery = dbContext.Database
    .SqlQuery<Guid>($"EXEC GetChildIdsFromParentWorkflowInstance {request.Id}")
    .AsQueryable();

var query = dbContext.WorkflowInstanceStepHistories.AsNoTracking()
    .Where(wish => relatedWorkflowIdsQuery.Contains(wish.WorkflowInstanceId.Value));

// 保留原有的Include逻辑
var queryWithIncludedData = await query
    .Include(wish => wish.WorkflowStepOutcome).AsNoTracking()
    .Include(wish => wish.WorkflowInstanceStep)
        .ThenInclude(wis => wis.WorkflowStep)
            .ThenInclude(ws => ws.Workflow).AsNoTracking()
    .Include(wish => wish.WorkflowInstanceStep)
        .ThenInclude(wis => wis.WorkflowStep)
            .ThenInclude(ws => ws.WorkflowStepType).AsNoTracking()
    .ToListAsync();

这种写法会让EF生成类似WHERE WorkflowInstanceId IN (EXEC GetChildIdsFromParentWorkflowInstance @requestId)的子查询，能直接利用WorkflowInstanceId上的索引。

2. 使用表值参数（TVP）传递ID列表 #

如果必须提前获取ID列表，可通过SQL Server的表值参数替代OPENJSON，让EF生成高效的JOIN查询：

第一步：在SQL Server中创建表值类型

CREATE TYPE GuidList AS TABLE (Id UNIQUEIDENTIFIER);

第二步：在EF中使用TVP查询

// 获取ID列表
var relatedWorkflowIds = await dbContext.Database
    .SqlQuery<Guid>($"EXEC GetChildIdsFromParentWorkflowInstance {request.Id}")
    .ToListAsync();

// 转换为符合表值类型的DataTable
var idTable = new DataTable();
idTable.Columns.Add("Id", typeof(Guid));
foreach (var id in relatedWorkflowIds)
{
    idTable.Rows.Add(id);
}

// 用表值参数关联查询
var query = dbContext.WorkflowInstanceStepHistories.AsNoTracking()
    .FromSqlRaw(@"
        SELECT w.*
        FROM WorkflowInstanceStepHistories w
        INNER JOIN @IdList ids ON w.WorkflowInstanceId = ids.Id",
        new SqlParameter("@IdList", SqlDbType.Structured)
        {
            TypeName = "GuidList",
            Value = idTable
        })
    .Include(wish => wish.WorkflowStepOutcome).AsNoTracking()
    .Include(wish => wish.WorkflowInstanceStep)
        .ThenInclude(wis => wis.WorkflowStep)
            .ThenInclude(ws => ws.Workflow).AsNoTracking()
    .Include(wish => wish.WorkflowInstanceStep)
        .ThenInclude(wis => wis.WorkflowStep)
            .ThenInclude(ws => ws.WorkflowStepType).AsNoTracking();

var queryWithIncludedData = await query.ToListAsync();

3. 辅助优化措施 #

• 添加索引：确保WorkflowInstanceStepHistories表的WorkflowInstanceId字段有非聚集索引，这是提升IN/JOIN查询性能的基础。
• 拆分查询：多Include会导致大笛卡尔积，可通过配置拆分查询优化：

  // 在DbContext的OnConfiguring方法中配置
  optionsBuilder.UseSqlServer(connectionString, opts =>
  {
      opts.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
  });
  

• 升级EF Core版本：新版本对Contains方法的处理逻辑更优，可调整阈值控制何时生成IN子句而非OPENJSON。

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