EF Core 8 WHERE IN搭配OPENJSON性能低下，求SQL/EF优化方案

EF Core 侧的解决办法 #

1. 全局强制用回传统 WHERE IN #

直接在EF Core的SQL Server配置里指定兼容级别，让它生成旧版SQL语法，不用OPENJSON。在DbContext的配置代码里加：

optionsBuilder.UseSqlServer(connectionString, options =>
{
    // 这里不是改数据库本身的兼容级别，只是让EF生成SQL Server 2016兼容的语句
    options.SetSqlGenerationCompatibilityLevel(130);
});

这个设置会让所有Contains查询回到WHERE IN(...)的写法，不用改任何业务代码，适合你的数百个查询场景。

2. 用拦截器针对性替换SQL #

如果不想全局改，可以写个命令拦截器，把生成的OPENJSON查询替换成IN子句。示例代码：

public class OpenJsonToInInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        var sql = command.CommandText;
        // 匹配EF Core生成的OPENJSON格式，需根据实际SQL调整正则规则
        var regex = new Regex(@"WHERE\s+(\[.*?\]\.\[.*?\])\s+IN\s+\(SELECT\s+\[value\]\s+FROM\s+OPENJSON\(@p(\d+)\)\)");
        var match = regex.Match(sql);
        if (match.Success)
        {
            var column = match.Groups[1].Value;
            var paramIndex = match.Groups[2].Value;
            var param = command.Parameters[$"@p{paramIndex}"];
            if (param.Value is string json)
            {
                // 解析JSON数组为参数列表
                var ids = JsonSerializer.Deserialize<List<int>>(json);
                var newParams = ids.Select((id, idx) =>
                {
                    var newParam = command.CreateParameter();
                    newParam.ParameterName = $"@p{paramIndex}_{idx}";
                    newParam.Value = id;
                    return newParam;
                }).ToList();
                // 替换SQL语句
                var inClause = string.Join(", ", newParams.Select(p => p.ParameterName));
                sql = regex.Replace(sql, $"WHERE {column} IN ({inClause})");
                // 更新命令参数
                command.Parameters.Remove(param);
                newParams.ForEach(p => command.Parameters.Add(p));
            }
        }
        command.CommandText = sql;
        return base.ReaderExecuting(command, eventData, result);
    }
}

然后注册拦截器：

optionsBuilder.AddInterceptors(new OpenJsonToInInterceptor());

这个方法更灵活，只替换目标查询，但要注意正则匹配的准确性，避免误改其他SQL。

3. 手动写SQL（适合性能敏感的查询） #

对个别慢查询，直接用FromSqlRaw写传统IN查询：

var ids = new List<int> { 101, 102, 103 };
var paramPlaceholders = string.Join(", ", ids.Select((_, i) => $"@p{i}"));
var data = context.YourEntities
    .FromSqlRaw($"SELECT * FROM YourEntities WHERE Id IN ({paramPlaceholders})", ids.ToArray())
    .ToList();

这样完全绕开EF的自动生成逻辑，直接使用高效的IN写法。

SQL Server 2017 侧的优化 #

1. 给OPENJSON查询加临时表缓存 #

EF生成的OPENJSON是直接关联查询，你可以通过自定义SQL或拦截器改成先把JSON解析到临时表再关联，比如：

DECLARE @jsonIds NVARCHAR(MAX) = @p0;
CREATE TABLE #TempIds (Id INT PRIMARY KEY);
INSERT INTO #TempIds SELECT CAST([value] AS INT) FROM OPENJSON(@jsonIds);
SELECT * FROM YourEntities e WHERE e.Id IN (SELECT Id FROM #TempIds);
DROP TABLE #TempIds;

临时表的主键索引能大幅提升关联效率，比直接用OPENJSON子查询快很多。

2. 用查询存储强制好的执行计划 #

开启数据库的查询存储，找到用OPENJSON的慢查询，然后强制使用传统IN查询的执行计划：

1. 开启查询存储：ALTER DATABASE [YourDbName] SET QUERY_STORE = ON;
2. 在SSMS的查询存储面板找到对应的慢查询，查看现有执行计划
3. 如果有传统IN查询的历史执行计划（之前跑过的高效计划），直接强制启用它，让后续的OPENJSON查询复用这个计划

3. 更新统计信息+检查索引 #

确保你的Id列有主键或非聚集索引，然后更新表的统计信息：

UPDATE STATISTICS [YourEntities];

过时的统计信息会让SQL Server生成糟糕的执行计划，更新后可能会改善OPENJSON的查询性能。

内容的提问来源于stack exchange，提问作者James Hill