Entity Framework Core 8 Where IN 与 Where IN OPENJSON-linq-IT问答社区-解决你的IT疑问

Entity Framework Core 8 Where IN 与 Where IN OPENJSON

Adil 2月前

57 0

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 端的选项（但不想更改数据库的兼容级别）。

帖子版权声明 1、本帖标题：Entity Framework Core 8 Where IN 与 Where IN OPENJSON
本站网址：http://xjnalaquan.com/
2、本网站的资源部分来源于网络，如有侵权，请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点，并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
5、站长邮箱：yeweds@126.com 除非注明，本帖由Adil在本站《linq》版块原创发布，转载请注明出处！

最新回复 (0)

最新倒序只看楼主

Arthur Boucher 2月前 0 只看Ta

2楼

我也遇到过这个问题。似乎使用 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());

在我看来，这似乎是一个合理的解决方案，但我以前当然也犯过错。任何反馈都值得感激。

user26583655 2月前 0 只看Ta

引用 3楼

感谢您总结这一切。自从有人在评论中分享了这个问题后，我就一直在 github 上关注这个问题。EF 团队的这种倒退有点令人震惊。我希望他们能尽快处理这个问题。
Ceres 2月前 0 只看Ta

引用 4楼
根据文档，防止使用 OPENJSON 的唯一方法是将 DB 兼容级别更改为 120（低于 SQL Server 2016）。这不是理想的解决方案。还有其他方法可以限制/防止使用 OPENJSON 吗？

您不必将 DB 兼容级别更改为 120。您可以调用 UseCompatibilityLevel(120) 来告诉 EF 生成旧式 SQL，而不管兼容级别实际设置为何。

还可以选择避免这种全局设置，而只将有问题的特定查询常量化。但我认为这种 EF.Constant 方法尚未进入正式版本，因此您需要使用每日构建。

SQL Server 上是否有一些配置可以提高 OPENJSON 的性能？从分析器数据可以清楚地看出，在使用 OPENJSON 时，SQL Server 无法正确使用索引。

它当然可以使用索引， OPENJSON 但可能选择不使用。

当你传递常量时它可以确定
- 您要传递多少件物品？
- 您传递了多少个重复项？如果有的话？
- 您传递的确切值是什么？（如果您的基数有偏差，这可能会很有用）
由于 OPENJSON 无法判断任何情况，因此只能依靠猜测。这可能会导致不同的（更糟糕的）计划。
thirtydot 2月前 0 只看Ta

引用 5楼

我们看到了完全相同的问题。由于 OpenJSON，更新到 EF8 导致了严重的性能问题。是的，它保存了查询计划，但查询本身导致了严重的性能问题。希望这是一个选项，类似于 .ContainsWithOpenJson 或 .ContainsAsParameterized 由于其他原因，我们无法反转兼容模式，但我们经常使用 .Contians。这对我们来说是个大问题。
thai_green_curry 2月前 0 只看Ta

引用 6楼

@Charlieface 为什么查询的索引要求会有所不同？他们都在问同一个问题，对吧？
akai 2月前 0 只看Ta

引用 7楼

根据我的经验，当您有大量项目时，它是值得使用的。对于这些少量项目，您最好使用链式“或”谓词生成查询。理想情况下，EF 会在查询转换器中设置一些开关来自动执行此操作，但它没有。总而言之，我体验到它在大量项目方面有巨大的改进，不仅在性能方面，而且还因为它可以防止查询计划污染。
BobbyShaftoe 2月前 0 只看Ta

引用 8楼

SQL Server 2016 不再受主流支持（甚至 2017 也不例外），因此 EF 8 团队不必将自己限制在仅支持 2016 的功能上