ORA-00904 with Entity Framework on Oracle 11g

ORA-00904 with Entity Framework on Oracle 11g

Postby hoekki » Fri 10 Mar 2017 09:22

Hi guys,

We are having a slight problem with the queries dotConnect for Oracle produces in Entity Framework.
Our software uses a few quite complex queries, which are currently implemented using Entity Framework 6.1.3. Some of these fail when using an Oracle 11g (Express) server, but succeed when using an Oracle 12c server. Failure of these queries is indicated by an ORA-00904 "invalid identifier 'Extend1'.'ID'" exception. These queries are based to some degree on inheritance in the EF object model and quite a few cascaded .Any(...) calls on m to n relationships.
Deeper analysis of the queries shows that dotConnect generates queries using a structure, only Oracle 12c accepts. Researching the root of this problem lead to this post:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531834700346297391
which perfectly describes the probem we are having.
A workaround would be implementing the queries directly in sql, but since the whole reason for using Entity Framework and dotConnect was not having to do that, this would be a major step back. Some of the queries in question are generated dynamically based on user input. In addition we use both Oracle and MsSql with our software. Implementing hard-coded sql queries is not really an option.
We would really appreciate it if you could fix this.

Best Regards

Hoekki

Using Entity Framework 6.1.3, .net 4.5.2, Oracle 11g Express (x64) and Oracle 12c (x64), dotConnect for Oracle 8.5.506 and 9.2.187.
hoekki
 
Posts: 6
Joined: Thu 21 Nov 2013 14:30

Re: ORA-00904 with Entity Framework on Oracle 11g

Postby Shalex » Wed 15 Mar 2017 19:45

Your explanation of the issue is correct. You have encountered a limitation of Oracle 11g. Also there is the corresponding entry in our documentation: https://www.devart.com/dotconnect/oracle/docs/faq.html#q108.

Our EF-provider generates Oracle-specific SQL basing on the expression tree which comes from EF engine. There is no universal solution for this problem with Oracle 10g and Oracle 11g. Please migrate to Oracle 12c if possible.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle