Changing query from == false to ! results in differing queries

[patrickklaeren]

Given the following model

public class Foo
{
    public int Id { get; set; }
    public string Address { get; set; }
    public string Postcode { get; set; }
    public bool Deleted { get; set; }
    public bool NeedsUpdating { get; set; }
}

Address and postcode are both optional/nullable.

When writing a query to pull all Foos where NeedsUpdating is true and the address or postcode are not null, changing == false to ! results in a different query.

I am unsure if this is a bug, a language quirk or something I am missing.

Queries:

 var foo = await _db.Foos
.Where(c => c.Deleted == false)
.Where(c => c.NeedsUpdating)
.Where(x => string.IsNullOrEmpty(x.Postcode) == false || string.IsNullOrEmpty(x.Address) == false)
 .AnyAsync();

results in the following SQL query:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Foos] AS [c]
        WHERE (([c].[Deleted] = CAST(0 AS bit)) AND ([c].[NeedsUpdating] = CAST(1 AS bit))) AND ((([c].[Postcode] IS NULL OR (([c].[Postcode] = N'') AND [c].[Postcode] IS NOT NULL)) AND (CAST(0 AS bit) = CAST(1 AS bit))) OR (([c].[Address] IS NULL OR (([c].[Address] = N'') AND [c].[Address] IS NOT NULL)) AND (CAST(0 AS bit) = CAST(1 AS bit))))) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

this does not yield the correct SQL query.

However, when changing == false to !, it works:

 var foo = await _db.Foos
.Where(c => c.Deleted == false)
.Where(c => c.NeedsUpdating)
.Where(x => !string.IsNullOrEmpty(x.Postcode) || !string.IsNullOrEmpty(x.Address))
 .AnyAsync();

results in the following SQL query:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Foos] AS [c]
        WHERE (([c].[Deleted] = CAST(0 AS bit)) AND ([c].[NeedsUpdating] = CAST(1 AS bit))) AND (([c].[Postcode] IS NOT NULL AND (([c].[Postcode] <> N'') OR [c].[Postcode] IS NULL)) OR ([c].[Address] IS NOT NULL AND (([c].[Address] <> N'') OR [c].[Address] IS NULL)))) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

Further technical details

EF Core version: 3.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: ASP.NET Core 3.0 (sdk 3.0.100)
Operating system: Windows 10 Pro 1903
IDE: Visual Studio 2019 16.3

[ajcvickers]

Note from triage: Since string.IsNullOrEmpty(x.Postcode) cannot return null there is no need for null semantics compensation here.

[nathan-alden-sr]

I stepped through EF Core's code (definitely not an expert in EF Core code) a bit and it looks like this may be happening for any IMethodCallTranslator that results in a bool expression. It also happens for string.IsNullOrWhiteSpace. The translation seems to simply ignore == false's existence.

[maumar]

with new refactorings in place we produce the same query in both cases:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Gears] AS [g]
        LEFT JOIN [Cities] AS [c] ON [g].[AssignedCityName] = [c].[Name]
        WHERE ([g].[Discriminator] IN (N'Gear', N'Officer') AND ([g].[HasSoulPatch] <> CAST(1 AS bit))) AND ([c].[Name] IS NOT NULL AND (([c].[Name] <> N'') OR [c].[Name] IS NULL))) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

fact that we unnecessarily apply null check to non-nullable function is tracked by #18555