We are so motivated that we have a long term solution in the works, butMy guess would be that Oracle will add "APPLY" statement support to its DBMS core in long term, therefore also eliminating this problem in EF.
I can't provide details at this point in time. This long term solution goes beyond just ODP.NET.
One would just need to know the input command tree expressionIn my opinion that is very difficult without source code. APPLY is used additionally along other generated tree nodes. And the oracle SQL equivalent to APPLY has to interact with the other generated SQL code (and most probably the entire command tree has to be interpreted differently when trying to replace the APPLY).
and provide the Oracle SQL equivalent to APPLY that would work in all instances.
Oracle Database 12c and ODP.NET 12c now support APPLY for lateral views. As long as you use these versions or higher, you no longer will see this LINQ error generated in your applications.
As described previously, to support APPLY required modifying Oracle's SQL dialect in order to accommodate LINQ's query generation.
we are currently using EF and Oracle 12c to develop a new application. At our development machines, everything works fine but somehow we get a topic-related exception on our customers test system.
It is "ORA-00907 missing right parenthesis" but its inner exception is "Oracle 188.8.131.52.0 doesn't support APPLY". Checking the file system, the registry and the oracle universal installer makes me sure that there is only the same 12c client installed as on out development machines. I can't find any hint for a 11.2 installation as the exceptions seems to indicate.
The problem occurs as soon as we use an include over 3 levels, like:
return dbContext.Table1.include(element => element.Table2.Table3.Table4);
Any ideas why the resulting APPLY works on some machines and not on others when 12c is installed on all of them?
Thanks in advance.
Just checked that and since it is 184.108.40.206.0, this at least explains the error message. But that doesn't explain why the error only occurs on the customer's test system. I've got it here by my side right now and I'm trying to connect to the same 220.127.116.11.0 server as with my machine. Both machines have the same 12c client installed, I know that because I made exactly the same setup procedure with the same installer on both systems. Both systems also have the same deploy of our application but still the error only occurs on the test system, not on mine.
Anyway, while writing this I found out what the problem is. My machine has .NET Framework 4.5 installed but since our application is targetting 4.0, there was just 4.0 installed on the test system. I just upgrade to 4.5 there and it works, which is bad since we want to stick with 4.0 to support Windows XP.
Does another version of .NET make the Entity Framework (same version) generate different statements?
Yes, that can happen. If you want definitively determine if the SQL generated between the versions is the same or different, you can use the ToTraceString method off of the EntityCommand to see what the actual SQL is.
Oracle has limited control over the SQL generated. LINQ controls the SQL generation for the most part.