ORA-00904: "FALSE": invalid identifier when .Any() is used in version Oracle.EntityFrameworkCore v 8.23.40

[Ruud2000]

After upgrading from Oracle.EntityFrameworkCore 8.21.140 to 8.23.40 queries in .NET Core using .Any() throw an exception ORA-00904: "FALSE": invalid identifier.

With version 8.21.140 a .Any() generates a SQL statement that returns a 0 or 1 like shown in the example below:

    if (context.Customers.Any())
    {
        return "We have customers!";
    }
    else
    {
        return "We have no customers!";
    }

SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM "MY_USER"."CUSTOMERS" "c") THEN 1
          ELSE 0
      END FROM DUAL

With version 8.23.40 a .Any() generates a SQL statement that returns True or False like shown in the example below:

SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM "MY_USER"."CUSTOMERS" "c") THEN True
          ELSE False
      END FROM DUAL

You can use the solution in this public repository for a runtime example of the issue. https://github.com/Ruud2000/OracleEntityFrameworkCoreBug/blob/main/README.md

[alexkeh]

This is expected behavior and a duplicate issue. The Oracle EF Core 23 provider will take the value set for OracleSQLCompatibility to know what DB version's SQL it should generate. By default, it will assume version 23 since it's ODP.NET 23. Oracle DB 23ai supports Boolean table columns. If you are using DB 19c, then you have to set OracleSQLCompatibility to 19 to tell ODP.NET to use numeric values instead of Booleans.

Here's more info on how to use this enumeration: https://github.com/oracle/dotnet-db-samples/issues/377#issuecomment-2096419703