Nullable Type comparison HasValue with boolean needs parentheses

[Isitar]

Hi

The Model looks something like this:

public class MyClass {
  public Guid Id {get;set;}
  public Guid? ParentId {get;set;}
}

then the query:

var valueFilterNegated = true;
... // some logic to set the valueFilterNegated

dbContext.MyClasses.Where(c =>valueFilter != c.ParentId.HasValue).CountAsync(cancellationToken);

the SQL Query produced is:

SELECT COUNT(*)::INT
FROM "MyClasses" AS s
WHERE ($1 <> s."ParentId" IS NOT NULL)

with $1 being either True or False, depending on the valueFilterNegated.

The Problem is, that the <> operator has a stronger binding than the IS NOT NULL and therefore it tries to compare boolean <> guid which throws: operator does not exist: boolean <> uuid at character

The solution is quite simple, put parantheses:

SELECT COUNT(*)::INT
FROM "MyClasses" AS s
WHERE ($1 <> (s."ParentId" IS NOT NULL))

[Isitar]

I think I found the issue:
This Commit: 313840d
removed the parantheses (part in NpgsqlQuerySqlGenerator) which causes the bug.

[roji]

Thanks for reporting and investigating - this is indeed because of 313840d. I did that because dotnet/efcore#23990 was supposed to fix this on the EF Core side, but it seems the fix targets boolean nullable columns only. Opened dotnet/efcore#26652 to do this for non-boolean columns as well, and submitted dotnet/efcore#26653 as a fix.

Let's see how it goes with that PR; if it ends up being serviced for EF Core 6.0.1, this will go away. Otherwise I can revert 313840d.

[roji]

OK, reverting this for 6.0.1 as a fix won't be going in for EF Core 6.0.1. Opened #2105 to track removing this again once fixed on the EF Core side.