# Performance Degradation in MS SQL Database Queries After Upgrading to EF Core 8: OPENJSON - Contains

**URL:** <https://www.virtocommerce.org/t/performance-degradation-in-ms-sql-database-queries-after-upgrading-to-ef-core-8-openjson-contains/731>\
**Category:** bug\
**Tags:** performance, efcore\
**Created:** [September 5, 2024, 3:44pm UTC](https://www.virtocommerce.org/t/performance-degradation-in-ms-sql-database-queries-after-upgrading-to-ef-core-8-openjson-contains/731 "2024-09-05T15:44:06Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![OlegoO](https://yyz2.discourse-cdn.com/flex032/user_avatar/www.virtocommerce.org/olegoo/32/27_2.png) [@OlegoO](https://www.virtocommerce.org/u/OlegoO)\
**Post date:** [September 5, 2024, 3:44pm UTC](https://www.virtocommerce.org/t/performance-degradation-in-ms-sql-database-queries-after-upgrading-to-ef-core-8-openjson-contains/731/1 "2024-09-05T15:44:06Z")

</div>

Hello Virto Commerce Community,

After upgrading to EF Core 8 (EFC8), we’ve encountered several severe performance regressions where previously millisecond queries started timing out. This issue is particularly evident in our Catalog, Order and Cart database queries.

## Analysis

The root cause appears to be related to the new translation of the `Contains` method to `OPENJSON`. The cardinality estimator assumes that `OPENJSON` will return huge amount of rows, which can dissuade SQL Server from seeking the index if the column being filtered is not very selective. This also affects the use of filtered indexes, which require constants.

In our case, many queries use `.Where(x => col.Contains(x.SomeId))`, where `col` typically contains just one element 99% of the time, with occasional cases of 0 or 2 elements.

**Orignal EFCore8 Query**

```sql
exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND [c].[Status] IN (
    SELECT [c0].[value]
    FROM OPENJSON(@__criteria_Statuses_0) WITH ([value] nvarchar(64) ''$'') AS [c0]
)
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @ __p_1 ROWS FETCH NEXT @__ p_2 ROWS ONLY',N'@__criteria_Statuses_0 nvarchar(4000),@__p_1 int,@ __p_2 int',@__ criteria_Statuses_0=N'["Completed"]',@ __p_1=0,@__ p_2=10

```

 ![image](https://canada1.discourse-cdn.com/flex032/uploads/virtocommerce/original/2X/0/0a8b71ae476a672f93207bded99f79aad8449d9d.png)

During the investigation, I found [GitHub Issue #32394](https://github.com/dotnet/efcore/issues/32394)

Below you can find the summary and my solution.

## Workaround

To mitigate this issue, we had to apply the `CompatLevel 120` hack, but this is not ideal as it limits our ability to use newer `ToJson` features.

```cs
            builder.UseSqlServer(connectionString, db => db.
                .UseCompatibilityLevel(120));

```

**EFCore8 Query**

```sql
exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND ([c].[Status] = N''Completed1'' OR [c].[Status] = N''Completed2'' OR [c].[Status] = N''Completed'3')
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @ __p_0 ROWS FETCH NEXT @__ p_1 ROWS ONLY',N'@ __p_0 int,@__ p_1 int',@ __p_0=0,@__ p_1=10

```

**Execution Plan**

 ![image](https://canada1.discourse-cdn.com/flex032/uploads/virtocommerce/original/2X/9/93061431301a222ece9c5a3f8744967b14397a8d.png)

## Future Solutions

There are two main approaches to address this issue without relying on the `CompatLevel 120` workaround:

1. **Wait for EF9/EF10** (scheduled for release in November):

2. **Opt-out of parameterization per query** :

We haven’t measured the benefits of parameterization yet, so we’ll likely avoid risk and choose the first option. We may later identify frequently run `WHERE-IN` queries and try opting in for them.

## Virto Team Workaround

In the meantime, we’ve implemented a custom workaround using C# code to transform `Contains` into multiple `OR` conditions, generating SQL queries without `OPENJSON`.

```csharp
protected virtual Expression<Func<TEntity, bool>> ContainsIn<TEntity, TProperty>(
    Expression<Func<TEntity, TProperty>> propertyExpression, IList<TProperty> values)
{
    var parameter = propertyExpression.Parameters[0];
    var property = propertyExpression.Body;
    var outerPredicate = PredicateBuilder.False<TEntity>();

    foreach (var value in values)
    {
        Expression<Func<TProperty>> valueSelector = () => value;
        var param = valueSelector.Body;
        var equalsExpression = Expression.Equal(property, param);
        outerPredicate = outerPredicate.Or(Expression.Lambda<Func<TEntity, bool>>(equalsExpression, parameter));
    }

    return outerPredicate;
}

```

**Sample Usage:**

```csharp
query = query.Where(ContainsIn<CustomerOrderEntity, string>(x => x.Status, criteria.Statuses));

```

**Generated SQL Code by EF Core 8:**

```sql
exec sp_executesql N'SELECT [c].[Id]
FROM [CustomerOrder] AS [c]
WHERE [c].[IsPrototype] = CAST(0 AS bit) AND ([c].[Status] = @__value_0 OR [c].[Status] = @__value_1)
ORDER BY [c].[CreatedDate] DESC, [c].[Id]
OFFSET @ __p_2 ROWS FETCH NEXT @__ p_3 ROWS ONLY',N'@__value_0 nvarchar(64),@__value_1 nvarchar(64),@ __p_2 int,@__ p_3 int',@ __value_0=N'Completed',@__ value_1=N'Completed3',@ __p_2=0,@__ p_3=10

```

**Execution Plan**

 ![image](https://canada1.discourse-cdn.com/flex032/uploads/virtocommerce/original/2X/8/8296a54de9c8d2e21334013997b17301ee199d92.png)

## Summary

We hope this information is helpful for anyone facing similar issues. Feel free to share your experiences or ask questions! And waiting for EFCore 9 and 10.