ODAC 11.2 Release 4 (11.2.0.3.0) throwing "Oracle 11.2.0.2.0 does not support APPLY" exception

Question

I'm using Entity Framework with the new ODAC that oracle just released.

My problem is that some of my queries that were working in previous beta releases stopped working in this new release version.

Here's an example:

IQueryable<SYS_PERFIL> query1 = m.DBContext.SYS_PERFIL.Where(T => T.SYS_UTILIZADOR.Where(TT => TT.ACTIVO == 1).Count() > 0);

IQueryable<SYS_PERFIL> query2 = m.DBContext.SYS_PERFIL.Where(T => T.SYS_UTILIZADOR.FirstOrDefault(TT => TT.ACTIVO == 1) != null);

string test1Query = ((System.Data.Objects.ObjectQuery)query1).ToTraceString();

string test2Query = ((System.Data.Objects.ObjectQuery)query2).ToTraceString();

var test1 = query1.ToList();

var test2 = query2.ToList();

While test1 returns the currect result, test2 throws an oracle exception 'ORA-00905: missing keyword' with message: 'Oracle 11.2.0.2.0 does not support APPLY'.

I don't want to re-test all of my methods. Is there a way to tell EF not to use apply?

Here are the queries that EF is generating:

test1Query:

SELECT "Project1"."PERFIL_ID" AS "PERFIL_ID" FROM ( SELECT "Extent1"."PERFIL_ID" AS PERFIL_ID", (SELECT COUNT(1) AS "A1" FROM "SMI2012"."SYS_UTILIZADOR" "Extent2" WHERE (("Extent1"."PERFIL_ID" = "Extent2"."PERFIL_ID") AND (1 = "Extent2"."ACTIVO"))) AS "C1"
 FROM "SMI2012"."SYS_PERFIL" "Extent1")  "Project1" WHERE ("Project1"."C1" > 0)

test2Query:

SELECT "Extent1"."PERFIL_ID" AS "PERFIL_ID" FROM  "SMI2012"."SYS_PERFIL" "Extent1" CROSS APPLY  (SELECT "Extent2"."PERFIL_ID" AS "PERFIL_ID", "Extent2"."ACTIVO" AS "ACTIVO", "Extent2"."USER_ID" AS "USER_ID" FROM "SMI2012"."SYS_UTILIZADOR" "Extent2" WHERE (("Extent1"."PERFIL_ID" = "Extent2"."PERFIL_ID") AND (1 = "Extent2"."ACTIVO")) AND (ROWNUM <= (1) ) ) "Element1" WHERE ("Element1"."USER_ID" IS NOT NULL)

Thanks in advance.

Answer 1

This will only work in 12c. In 11g you have to re-write the query.

IQueryable<SYS_PERFIL> query2 = from one in m.DBContext.SYS_PERFIL
  join two in m.DBContext.SYS_UTILIZADOR on one.COL1 equals two.COL1
  where two.ACTIVO == 1
  select one;

This will generate duplicates if you have more than one active SYS_UTILIZADOR. You would need to fire distinct on that in that case. This will not result in super great performance on large number of rows. Should be OK on a small number.

Answer 2

Have you tried Oracle 12c?

Here https://forums.oracle.com/message/10168766#10168766

read that should works.

Answer 3

The Telerik OpenAccess ORM does not use the APPLY statement. I had the same issue on a nested group query. When I built the OA model, the query ran great!