ORA-00904 with Entity Framework on Oracle 11g

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hoekki
Posts: 8
Joined: Thu 21 Nov 2013 14:30

ORA-00904 with Entity Framework on Oracle 11g

Post by 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? ... 0346297391
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.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

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

Post by 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/oracl ... .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.

Post Reply