EF Core 8 现在使用 WHERE IN 和 OPENJSON 的组合在 where 语句中内联值，而不是以前的 WHERE IN(...)。此更改已在文档中注明，并说明了原因……
EF Core 8 现在使用 ，而 的组合在 where 语句中内联值 WHERE IN and OPENJSON 不是以前的 WHERE IN(...) .
文档中记录了 这一变化 ，并说明了原因如下：
此处内联值的方式使得 SQL 注入攻击不存在任何可能性。下文描述的使用 JSON 的更改完全是为了性能，与安全性无关。
不幸的是， OPENJSON 我们的 2017 SQL Server 实例的性能很差。
以下查询由 EF Core 8 生成， 运行 1.8 秒 400,000 次读取 ：
DECLARE @__scheduleTagIds_0 nvarchar(4000) = N'[5835,5970,6563,6564,6565,6645,6835,6850,7034,7127]';
SELECT [s].[ScheduleTagId]
,[s].[MustStartProdBy]
FROM [ScheduleTagMustStartBy] AS [s]
WHERE [s].[ScheduleTagId] IN (
SELECT [s0].[value]
FROM OPENJSON(@__scheduleTagIds_0) WITH ([value] int '$') AS [s0]
)
如果我重构查询以使用标准 WHERE IN(...) ，则执行时间将下降到 120ms 和 29,000 次读取 ：
SELECT [s].[ScheduleTagId]
,[s].[MustStartProdBy]
FROM [ScheduleTagMustStartBy] AS [s]
WHERE [s].[ScheduleTagId] IN (5835,5970,6563,6564,6565,6645,6835,6850,7034,7127)
我的应用程序中有数百个查询 .Where(x => [collection].Contains(x.Id)) ，我非常担心在选择查询中看到的性能下降。
问题
我该怎么做才能缓解这个问题？我愿意选择 EF 或 SQL Server 端的选项（但不想更改数据库的兼容级别）。
我也遇到过这个问题。似乎使用 TOP 可以将列表中包含少量项目的查询与包含大量项目的查询区分开来，假设其中包含约 50 个项目可能非常糟糕。
例如，可以使用 DbCommandInterceptor 修改 SQL。示例实现可能如下所示：
/// <summary>Contains query optimize interceptor</summary>
/// <remarks>
/// A problem with contains queries translated into OPENJSON are that SQL server cannot determine how many rows the query will yield.
/// We will therefore divide queries into groups of different sizes to provide SQL server with a clear max count to allow for better
/// query paths to be taken
/// </remarks>
public partial class SqlServerContainsQueryOptimizeInterceptor : DbCommandInterceptor {
private static readonly int[] sizeGroups = [1, 2, 5, 10, 20, 50];
/// <inheritdoc/>
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result) {
ManipulateCommand(command);
return result;
}
/// <inheritdoc/>
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default) {
ManipulateCommand(command);
return new ValueTask<InterceptionResult<DbDataReader>>(result);
}
/// <inheritdoc/>
public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result) {
ManipulateCommand(command);
return result;
}
/// <inheritdoc/>
public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default) {
ManipulateCommand(command);
return new ValueTask<InterceptionResult<object>>(result);
}
/// <summary>Handle the actual command manipulation</summary>
private static void ManipulateCommand(DbCommand command) {
var sql = command.CommandText;
var newSql = openJsonMatcher.Replace(sql, m => {
try {
// Read the parameter value to determine the size of the array
var paramName = m.Groups[1].Value;
var paramValue = command.Parameters[paramName].Value?.ToString();
if (paramValue == null) return m.Value;
var items = JsonNode.Parse(paramValue)!.AsArray();
// Replace the select statement with a top statement to help the query planner
var group = sizeGroups.FirstOrDefault(x => x >= items.Count);
return group != 0
? m.Value.Replace("SELECT", "SELECT TOP " + group)
: m.Value;
}
catch {
return m.Value;
}
});
if (newSql != sql) {
command.CommandText = newSql;
}
}
private static readonly Regex openJsonMatcher = GetOpenJsonRegEx();
[GeneratedRegex(@"IN\s+\(\s*SELECT\s+[^\s]+\s+FROM\s+OPENJSON\(([^)]+)\)(?: WITH \([^)]+\))?[^)]+\)")]
private static partial Regex GetOpenJsonRegEx();
}
然后可以在设置 SQL 服务器时将拦截器添加到 opts 构建器中，例如
optionsBuilder.UseSgsSql(connectionStr);
optionsBuilder.AddInterceptors(new SqlServerContainsQueryOptimizeInterceptor());
在我看来，这似乎是一个合理的解决方案，但我以前当然也犯过错。任何反馈都值得感激。