Devart Forums

Posted: **Tue 21 Apr 2020 06:30**

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 ONLYusps tracking showbox speed test

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

Posted: **Tue 05 May 2020 14:37**

The bug with sorting by CLOB/NCLOB column in EF Core is fixed in v9.6.696.

With the newest (9.11.980) build your project generates:

Code: Select all

SELECT "$it".JJIS_NUMBER AS "Value0", "$it".YOUTH_ID AS "Value"
FROM YOUTH "$it"
ORDER BY DBMS_LOB.SUBSTR("$it".JJIS_NUMBER), "Value"
FETCH FIRST :p__TypedProperty_0 ROWS ONLY

Please upgrade.

Devart Forums

Invalid SQL is being generated by dotConnect for Oracle (EFCore)!!!

Invalid SQL is being generated by dotConnect for Oracle (EFCore)!!!

Re: Invalid SQL is being generated by dotConnect for Oracle (EFCore)!!!