Compatibility issue on Boolean mapping

[BehroozBahrameh]

When you have an entity with a boolean column like

[Table("TEST_TABLE", Schema = "BB")]
public class TestEntity
{
    [Column("ID")] public long Id { get; set; }
    [Column("IsTrue")] public IsTrue int  { get; set; }
}

and a domain record like:

public record Test(
    long Id,
    bool IsTrue);

and use AutoMapper for mapping those two like:

public class TestProfile : Profile
{
    public TestProfile()
    {
        CreateMap<TestEntity, Test>();
    }
}

and try to retrieve data with like:

_mapper.ProjectTo<Test>(
            _dbContext.TestEntity.Take(10)
).ToArrayAsync();

the generated query from the version 8.21.140 is,

SELECT "a"."ID", CASE
          WHEN "a"."IsTrue" = -1 THEN 1
          ELSE 0
      END
      FROM "BB"."TEST_TABLE" "a"
      FETCH FIRST :p_0 ROWS ONLY

and from version 8.23.40 is

SELECT "a"."ID", CASE
          WHEN "a"."IsTrue" = -1 THEN True
          ELSE False
      END
      FROM "BB"."TEST_TABLE" "a"
      FETCH FIRST :p_0 ROWS ONLY

and therefore TRUE and FALSE do not exist on Oracle, by the latest version of the Oracle drive (8.23.40) you get an exception. Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "FALSE": invalid identifier and everything is fine in the previous versions!

[alexkeh]

In the 23ai release, we've added new OracleSQLCompatibility enumeration values:

• OracleSQLCompatibility.DatabaseVersion19
• OracleSQLCompatibility.DatabaseVersion21
• OracleSQLCompatibility.DatabaseVersion23 By default, the enumeration value will match the ODP.NET version number. In the case of ODP.NET 23, the enumeration is OracleSQLCompatibility.DatabaseVersion23.

In DB 23ai, BOOLEAN columns and native JSON columns are supported. Thus, Oracle EF Core 8.23.40 use these in its SQL when OracleSQLCompatibility.DatabaseVersion23 or no value is set. If you'd like to revert to 19c compatible behavior, use the OracleSQLCompatibility.DatabaseVersion19 value.

[woha]

Thank you very much @BehroozBahrameh for opening this issue and thank you @alexkeh for your answer - this saved me from some trouble!

[BehroozBahrameh]

thank you so much for your reply @alexkeh. the issue is solved by explicitly defining UseOracleSQLCompatibility I have a question, mostly out of curiosity, What is the recommended way to check breaking changes as versioning does not show it ( or maybe I have a wrong interpretation )?

[BehroozBahrameh]

In the 23ai release, we've added new OracleSQLCompatibility enumeration values:

• OracleSQLCompatibility.DatabaseVersion19
• OracleSQLCompatibility.DatabaseVersion21
• OracleSQLCompatibility.DatabaseVersion23 By default, the enumeration value will match the ODP.NET version number. In the case of ODP.NET 23, the enumeration is OracleSQLCompatibility.DatabaseVersion23.

In DB 23ai, BOOLEAN columns and native JSON columns are supported. Thus, Oracle EF Core 8.23.40 use these in its SQL when OracleSQLCompatibility.DatabaseVersion23 or no value is set. If you'd like to revert to 19c compatible behavior, use the OracleSQLCompatibility.DatabaseVersion19 value.

[alexkeh]

@BehroozBahrameh The breaking change is documented here. It's a new enumeration that replaces a string value, but also has a different default than its replacement as older Oracle DB versions have reached end of support.

In the next doc version, we'll make the new default behavior clearer in this breaking change section.

[lwestfall]

Note to self: read closed issues before spending half a day on a repro. Glad compatibility can be set though!