UseRelationalNulls causes subquery to include NOT IN (NULL, x)

[forenpm]

This query produces an invalid WHERE when UseRelationalNulls = true for nullable int.
Incorrect SQL producted: AND [a].[StatusCode] NOT IN (NULL, 3)

await db.ProjectModels
  .Where(p => p.ProjectId == projectid)
  .Select(p => new ScoreRemainingActivitiesInput
  {
      RemainingActivities = p.Activities
          .Where(a => a.TaskType != TaskTypeEnum.TT_WBS && a.TaskType != TaskTypeEnum.TT_LOE)
          .Where(a => a.StatusCode != null && a.StatusCode != StatusCodeEnum.TK_Complete)
          .Count()
  }).FirstOrDefaultAsync();

SQL when UseRelationalNulls = false.

SELECT TOP (1) (
    SELECT COUNT(*)
    FROM [ActivityModels] AS [a]
    WHERE (
        ([p].[ProjectId] = [a].[ProjectId])
        AND (
          [a].[TaskType] NOT IN (6, 3)
          OR [a].[TaskType] IS NULL
          )
        )
      AND (
        ([a].[StatusCode] <> 3)
        AND [a].[StatusCode] IS NOT NULL
        )
    ) AS [RemainingActivities]
FROM [ProjectModels] AS [p]
WHERE [p].[ProjectId] = '1A66B859-F096-4595-8B30-D127D8CA6E0E'

SQL when UseRelationalNulls = true.

SELECT TOP (1) (
    SELECT COUNT(*)
    FROM [ActivityModels] AS [a]
    WHERE (
        ([p].[ProjectId] = [a].[ProjectId])
        AND [a].[TaskType] NOT IN (6, 3)
        )
      AND [a].[StatusCode] NOT IN (NULL, 3)
    ) AS [RemainingActivities]
FROM [ProjectModels] AS [p]
WHERE [p].[ProjectId] = '1A66B859-F096-4595-8B30-D127D8CA6E0E'

EF Core version: 5.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.8.3

[maumar]

This is by design, when relational null semantics is switched on, EF Core naively translates null comparisons. There is an optimization which compacts multiple comparisons into IN/NOT IN, but the query yields the same results if the optimization is not applied, i.e. [a].[StatusCode] <> 3 AND [a].[StatusCode] <> NULL rather than [a].[StatusCode] NOT IN (NULL, 3)

The purpose for relational null semantics is to get a bit of performance by skipping all the additional null checks/optimizations when you know that these checks are not necessary, e.g. when the database columns are nullable but they don't contain any nulls. Otherwise it is recommended to use clr null semantics to avoid data corruption errors.

[smitpatel]

a => a.StatusCode != null && a.StatusCode != StatusCodeEnum.TK_Complete

We should not combine above in InExpression if relational null semantics are on. Currently there is no way for user to write the comparison. If user writes a list.Contains where list has a null value then it is fine to generate above invalid SQL. User can explicitly check for null, if list contains it and remove the null values from list before calling Contains. (Funcletizer will take care of generate correct parameters and translating it)