Oracle.EntityFrameworkCore 8.23.40 produces invalid queries in Oracle 19c for boolean data type
troglas opened this issue · 2 comments
troglas commented
In Oracle.EntityFrameworkCore 8.23.40 the following query fails using 19c
CREATE TABLE "XXORA"."S90T1"
( "UNAME" CHAR(8 BYTE) DEFAULT ' ' NOT NULL ENABLE,
"UTYPE" NUMBER(*,0) DEFAULT 0 NOT NULL ENABLE,
CONSTRAINT "S90T1_PRIM_KEY" PRIMARY KEY ("UNAME")
)
TABLESPACE "ASTRO_TS_XXORA"
var users = db.Users.Select(u => new
{
Active = u.Utype == StandardUserType,
}).ToListAsync();
It gets translated to
SELECT CASE
WHEN "s"."UTYPE" = 9 THEN True
ELSE False
END, "s"."UTYPE"
FROM "XXORA"."S90T1" "s"
Then the result is:
ORA-00904: "FALSE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 3 Column: 10
Same query applied on 8.21.140 gives
SELECT CASE
WHEN "s"."UTYPE" = 9 THEN 1
ELSE 0
END, "s"."UTYPE"
FROM "XXORA"."S90T1" "s"
which works properly on 19c
OlegUfaev commented
You should use OracleSQLCompatibility.DatabaseVersion19: https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/EFCoreAPI.html#GUID-62B5F127-80BB-4047-8885-503627A50B5F
services.AddDbContextFactory<MyDbContext>(optionsBuilder => optionsBuilder
.UseOracle(connectionString, opt =>
{
opt.UseOracleSQLCompatibility(OracleSQLCompatibility.DatabaseVersion19);
}));