Invalid SQL is being generated by dotConnect for Oracle (EFCore)
Posted: Wed 30 Jan 2019 23:31
The following invalid SQL is being generated by dotConnect for Oracle
SELECT "$it".JJIS_NUMBER AS "Value0", "$it".YOUTH_ID AS "Value"
FROM OYA.YOUTH "$it"
ORDER BY DBMS_LOB.SUBSTR("$it"."Value0"), "Value"
FETCH FIRST :p__TypedProperty_0 ROWS ONLY
This leads to the following exception: Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "$it"."Value0": invalid identifier
To reproduce
1. Clone or download https://github.com/jwobbeoya/ReproDevartBug
2. Create the youth table with the Youth.sql script in the Ddl folder. The schema expected is OYA, but that can be changes if you update the schema in Consts.cs
3. Update appsetting.json with your connection string
4. Starting / Debugging the app with will cause invalid query to be generated
SELECT "$it".JJIS_NUMBER AS "Value0", "$it".YOUTH_ID AS "Value"
FROM OYA.YOUTH "$it"
ORDER BY DBMS_LOB.SUBSTR("$it"."Value0"), "Value"
FETCH FIRST :p__TypedProperty_0 ROWS ONLY
This leads to the following exception: Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "$it"."Value0": invalid identifier
To reproduce
1. Clone or download https://github.com/jwobbeoya/ReproDevartBug
2. Create the youth table with the Youth.sql script in the Ddl folder. The schema expected is OYA, but that can be changes if you update the schema in Consts.cs
3. Update appsetting.json with your connection string
4. Starting / Debugging the app with will cause invalid query to be generated