Issues around OPENJSON with a collection containing null

[BurakBebek1]

Bug description

Technical Root Cause
The exception occurs because when OPENJSON is used for a nullable collection, the mapping logic fails to handle the null element within the JSON projection.

The InvalidCastException is thrown when EF Core attempts to convert a null value from the collection into a non-nullable underlying type during the command execution phase.

The UnreachableException (DebugAssert) during translation confirms that SqlServerSqlNullabilityProcessor creates a ColumnExpression with a nullable type, which is not allowed by the SqlExpression base constructor.

Generated SQL (Incorrect)
When the parameter limit (>2100) is exceeded, EF Core switches to OPENJSON but fails to account for nullability in the collection:

SQL
SELECT [e].*
FROM [Entities] AS [e]
WHERE [e].[NullableInt] IN (
SELECT [__value].[value]
FROM OPENJSON(@values) WITH ([value] int '$') AS [__value]
)
Note: In SQL Server, NULL IN (...) or Column IN (SELECT NULL) never evaluates to true. Therefore, rows where NullableInt is NULL are incorrectly excluded from the results.

Expected Behavior / SQL
The query should handle the null case by adding an OR ... IS NULL clause, ensuring consistency with how EF Core handles standard (non-OPENJSON) parameterization:

SQL
SELECT [e].*
FROM [Entities] AS [e]
WHERE [e].[NullableInt] IN (
SELECT [__value].[value]
FROM OPENJSON(@values) WITH ([value] int '$') AS [__value]
) OR [e].[NullableInt] IS NULL -- This clause should be generated when the collection contains a null

Your code

[ConditionalFact]
public virtual async Task Parameter_collection_with_null_value_Contains_null_element()
{
    using var context = Fixture.CreateContext();

    var values = Enumerable.Range(1, 2200).Select(i => (int?)i).ToList();
    
    values.Add(null); 

    var query = await context.Set<PrimitiveCollectionsEntity>()
        .Where(e => values.Contains(e.NullableInt))
        .ToListAsync();

    var sql = Fixture.TestSqlLoggerFactory.SqlStatements.Last();
    
    Assert.Contains("OR [p].[NullableInt] IS NULL", sql);
}

Stack traces

1. During Execution (InvalidCastException): This error occurs when the ValueConverter attempts to process null values within the large collection during the command execution:

System.InvalidCastException : Object must implement IConvertible.
   at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
   at Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter`2.Sanitize[T](Object value)
   at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand command, String name, Object value, Nullable`1 nullable, ParameterDirection direction)
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypeMappedRelationalParameter.AddDbParameter(DbCommand command, Object value)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)

2. During SQL Processing (UnreachableException / DebugAssert): This error occurs when SqlServerSqlNullabilityProcessor attempts to build a ColumnExpression for the OPENJSON projection using a nullable type, which violates SqlExpression constraints:

System.Diagnostics.UnreachableException : Check.DebugAssert failed: SqlExpression.Type must be reference type or non-nullable value type
   at Microsoft.EntityFrameworkCore.Utilities.Check.DebugAssert(Boolean condition, String message) in C:\Projects\efcore\src\Shared\Check.cs:line 114
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlExpression..ctor(Type type, RelationalTypeMapping typeMapping) in C:\Projects\efcore\src\EFCore.Relational\Query\SqlExpressions\SqlExpression.cs:line 25
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.ColumnExpression..ctor(String name, String tableAlias, Type type, RelationalTypeMapping typeMapping, Boolean nullable) in C:\Projects\efcore\src\EFCore.Relational\Query\SqlExpressions\ColumnExpression.cs:line 34
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlNullabilityProcessor.VisitIn(InExpression inExpression, Boolean allowOptimizedExpansion, Boolean& nullable) in C:\Projects\efcore\src\EFCore.SqlServer\Query\Internal\SqlServerSqlNullabilityProcessor.cs:line 264

Verbose output

Not applicable (Runtime Query Issue). Full stack trace and reproduction code provided above.

EF Core version

11.0.0-alpha.1.26064.118

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 11

Operating system

Windows 11

IDE

Visual Studio 2022 17.14.16

[MihuBot]

I'm a bot. Here is a possible related and/or duplicate issue (I may be wrong):

• Contains call fails with System.InvalidCastException: Object must implement IConvertible if array contains null in 10.0.2 #37537

[roji]

@BurakBebek1 is this the same as #37537, which also has PR #37539 open as a fix?

[BurakBebek1]

@roji While the InvalidCastException symptom is similar to #37537, this issue (#37605) is specifically about the OPENJSON translation path when the parameter limit is exceeded (>2100 elements).

Unlike #37537, this bug also triggers a System.Diagnostics.UnreachableException (DebugAssert) in SqlServerSqlNullabilityProcessor.VisitIn because the translation logic fails to handle nullability for the projected JSON column. Furthermore, even if the exception is bypassed, the generated SQL is logically incorrect as it misses the OR ... IS NULL clause required for SQL Server's three-valued logic. Therefore, this represents a separate flaw in the OPENJSON implementation rather than just a general parameter mapping issue.