Closed
Description
openedon Dec 24, 2020
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
