This discussion is archived
12 Replies Latest reply: Sep 17, 2013 11:06 AM by Alex_Keh - Oracle_Product_Manager RSS

Entity Framework generates invalid oracle sql.

Invincible Newbie
Currently Being Moderated
I have similar tables:
create table ParameterSets
(
Id number(10, 0) not null,
Name nvarchar2(50) not null,
constraint ParameterSet_PK primary key (Id)
);

create table Parameters
(
Id number(10, 0),
Name nvarchar2(50),
ParameterSetId number(10, 0) not null,
constraint Parameters_PK primary key (Id),
constraint Parameters_ParamSet_FK foreign key (ParameterSetId) references ParameterSets(Id)
);

create table UserGroups
(
Id number(10, 0) not null,
Name nvarchar2(50) not null,
ParameterSetId number(10, 0) not null,
constraint UserGroups_PK primary key (Id),
constraint UserGroups_ParamSet_FK foreign key (ParameterSetId) references ParameterSets(Id)
);

create table Users
(
Id number(10,0) not null,
GroupId number(10,0) not null,
Name nvarchar2(50) not null,
constraint Users_PK primary key (Id),
constraint Users_Groups_FK foreign key (GroupId) references UserGroups(Id)
);

And I want to do a join between these tables using this statement:

var query = from u in entities.Users.Include("UserGroup.ParameterSet.Parameters")
where u.Id == 1
select u;

var user = query.SingleOrDefault();

This is very simple Linq, but it generates invalid Oracle SQL that contains OUTER APPLY.
I found that there is a bug to Microsoft for that but can it be fixed by Oracle provider ?
At the end which layer generates final SQL MS's (EF) or ODP's (The Provider) ?
because "query" sql generates invalid SQL but SingleOrDefault adds completely Oracle code "rownum < 2"

The sql is:
SELECT
"Project1"."ID2" AS "ID",
"Project1"."ID" AS "ID1",
"Project1"."GROUPID" AS "GROUPID",
"Project1"."NAME" AS "NAME",
"Project1"."ID1" AS "ID2",
"Project1"."NAME1" AS "NAME1",
"Project1"."PARAMETERSETID" AS "PARAMETERSETID",
"Project1"."ID3" AS "ID3",
"Project1"."NAME2" AS "NAME2",
"Project1"."C1" AS "C1",
"Project1"."ID4" AS "ID4",
"Project1"."NAME3" AS "NAME3",
"Project1"."PARAMETERSETID1" AS "PARAMETERSETID1"
FROM ( SELECT
     "Filter1"."ID1" AS "ID",
     "Filter1"."GROUPID" AS "GROUPID",
     "Filter1"."NAME1" AS "NAME",
     "Filter1"."ID2" AS "ID1",
     "Filter1"."NAME2" AS "NAME1",
     "Filter1"."PARAMETERSETID1" AS "PARAMETERSETID",
     "Filter1"."ID3" AS "ID2",
     "Filter1"."ID4" AS "ID3",
     "Filter1"."NAME3" AS "NAME2",
     "Filter2"."ID5" AS "ID4",
     "Filter2"."NAME4" AS "NAME3",
     "Filter2"."PARAMETERSETID2" AS "PARAMETERSETID1",
     CASE WHEN ("Filter2"."ID5" IS NULL) THEN NULL ELSE 1 END AS "C1"
     FROM (SELECT "Extent1"."ID" AS "ID1", "Extent1"."GROUPID" AS "GROUPID", "Extent1"."NAME" AS "NAME1", "Extent2"."ID" AS "ID2", "Extent2"."NAME" AS "NAME2", "Extent2"."PARAMETERSETID" AS "PARAMETERSETID1", "Extent3"."ID" AS "ID3", "Extent3"."NAME" AS "NAME5", "Extent3"."PARAMETERSETID" AS "PARAMETERSETID3", "Extent4"."ID" AS "ID4", "Extent4"."NAME" AS "NAME3"
          FROM "VENTSI"."USERS" "Extent1"
          INNER JOIN "VENTSI"."USERGROUPS" "Extent2" ON "Extent1"."GROUPID" = "Extent2"."ID"
          LEFT OUTER JOIN "VENTSI"."USERGROUPS" "Extent3" ON "Extent1"."GROUPID" = "Extent3"."ID"
          LEFT OUTER JOIN "VENTSI"."PARAMETERSETS" "Extent4" ON "Extent3"."PARAMETERSETID" = "Extent4"."ID"
          WHERE (1 = "Extent1"."ID") ) "Filter1"
     OUTER APPLY (SELECT "Extent5"."ID" AS "ID5", "Extent5"."NAME" AS "NAME4", "Extent5"."PARAMETERSETID" AS "PARAMETERSETID2", "Extent6"."ID" AS "ID6", "Extent6"."NAME" AS "NAME6", "Extent6"."PARAMETERSETID" AS "PARAMETERSETID4"
          FROM "VENTSI"."PARAMETERS" "Extent5"
          INNER JOIN "VENTSI"."USERGROUPS" "Extent6" ON "Extent6"."PARAMETERSETID" = "Extent5"."PARAMETERSETID"
          WHERE ("Filter1"."GROUPID" = "Extent6"."ID") ) "Filter2"
) "Project1"
ORDER BY "Project1"."ID2" ASC, "Project1"."ID" ASC, "Project1"."ID1" ASC, "Project1"."ID3" ASC, "Project1"."C1" ASC

I'm thinking that the shame is for EF team - this query is simple join between four tables.
select * from Users u
left join UserGroups ug on u.groupid = ug.Id
left join ParameterSets ps on ug.parametersetid = ps.id
left join Parameters p on ps.id = p.parametersetid
where u.id = 1
  • 1. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    The APPLY is generated by EF, not Oracle. Here's an excerpt from one of the EF READMEs on the issue.
    ============

    2.2.4 Query pipeline uses CROSS APPLY and OUTER APPLY on providers that do not support these operators

    In some cases, usually for queries that have correlated subqueries, the query pipeline might produce an output query that contains the CROSS APPLY or OUTER APPLY operators. Some backend providers, including versions of SQL Server earlier than SQL Server 2005, do not support these operators and cannot run the queries. Following are typical scenarios that may lead to CROSS APPLY and/or OUTER APPLY operators in the output query:

    * Multiset constructors in the select clause
    * A correlated subquery with paging
    * A correlated subquery with grouping and/or aggregates
    * Combining DEREF and REF constructs in the query
    * AnyElement construct in the selection
    * Explicitly specifying CROSS APPLY or OUTER APPLY
    * Calling RelatedEnd.Source when there is more than one entity set for the involved entity types
  • 2. Re: Entity Framework generates invalid oracle sql.
    anecker Newbie
    Currently Being Moderated
    Hi,
    it would be really cool if oracle provides a workaround for this problem... (convert the APPLY to something that the oracle database can understand)
    Regards
    Andreas
  • 3. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    Oracle would provide a workaround if it was possible. We've consulted with MS and no workaround is possible. We've asked MS to put a workaround in a future EF version, but have not heard any definitive plans yet. If you would like ODP.NET to support APPLY, please request MS to provide a way for the EF query pipeline to be be able to workaround this. Oracle will implement a fix if MS makes it possible.

    This issue is not Oracle-specific. It applies to any non-SQL Server DB, such as DB2 and MySQL, since they do not support APPLY either.
  • 4. Re: Entity Framework generates invalid oracle sql.
    934414 Newbie
    Currently Being Moderated
    Hello Alex,
    I'm affraid it's not that simple to blame Microsoft for this behaviour. The Entity Framework itself does not generate the SQL statements.
    It generates command trees. And it should be Oracle's job to translate these command trees into SQL statements that can (always) be interpreted
    by the oracle database. At least that would be expected of an officially released and supported EF Provider.

    A rudementary example on how this is done can be found here: http://oracleef.codeplex.com/ in the namespace SqlGen.
    That sample mostly uses 1:1 copies of the MSSQL query generation implementation.

    I understand that it is not nice of the EF provider to generate command trees that buid on an APPLY structure.
    But it should be possible for Oracle to translate/transform such situations into non-apply queries.


    The problem with such issues usually is that vendors blame each other and as a result it is never fixed. :(
    Of course priorities in such matters can also be politically motivated (for both Microsoft and Oracle).

    But this is a very critical issue. The way it is now, the Entity Framework is almost unusable with Oracle.
    Not unless you start re-learning/guessing which linq queries you can and which you cannot use inside your code
    (also hard to trace/predict, as the problem itself only occurs in runtime).

    Alternative: Can Oracle provide the source code for the EF-provider, so that the community can attempt to workaround this problem?

    Thanks in advance.
  • 5. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    PWC,
    My intention was not to blame MS. I was trying to explain it was not possible for ODP.NET to solve the problem by itself. It would require some changes from MS.

    MS is best suited to solve the issue because APPLY doesn't just affect Oracle. It exists in SQL Server as well in versions before APPLY became part of their SQL language. The problem also exists in DB2, MySQL, PostgreSQL, basically any DB that doesn't support APPLY already. DevArt and DataDirect, which have created Oracle EF providers, and have the same problem. Their business is to sell license to their Oracle providers and have no reason to hold back on functionality. They too do not support an APPLY-equivalent functionality because no data provider-only solution exists.

    Oracle and MS dev teams have discussed how to resolve this issue among each other and our SQL dev teams. The conclusion was there is no solution that a data provider could implement on its own.

    The sample provider on codeplex doesn't provide an example of how to workaround APPLY.

    Oracle won't share its ODP.NET source code, but we would be happy to implement an APPLY workaround if someone proposes a solution. There is no need to know how ODP.NET is implemented today if a solution is possible. One would just need to know the input command tree expression and provide the Oracle SQL equivalent to APPLY that would work in all instances.

    Oracle realizes this is a significant customer issue and are we very motivated to solve the problem. We are so motivated that we have a long term solution in the works, but I can't provide details at this point in time. This long term solution goes beyond just ODP.NET.
  • 6. Re: Entity Framework generates invalid oracle sql.
    934414 Newbie
    Currently Being Moderated
    Hello Alex, thanks for your response.
    We are so motivated that we have a long term solution in the works, but
    I can't provide details at this point in time. This long term solution goes beyond just ODP.NET.
    My guess would be that Oracle will add "APPLY" statement support to its DBMS core in long term, therefore also eliminating this problem in EF.


    It's too bad one can not set the SQL dialect to something equivalent to MSSQL Server 2000 via ProviderManifestToken attribute in EF4 any more. To my knowledge that was possible in earlier versions of the EntityFramework and didn't produce System.Data.SqlClient.SqlGen.SqlGenerator.OuterApplyOp tree nodes. EF4 only supports MSSQL 2005 or higher (correct me if I'm wrong).

    One would just need to know the input command tree expression
    and provide the Oracle SQL equivalent to APPLY that would work in all instances.
    In 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).

    The only way I can think of to get this done is to get a test project from ORCACLE with some cases that generate OuterApplyOp trees and then program against these test scenarios. The external developer would at least need a way to override part of your code and methods that currently interpret and generate sql code for the command trees.

    But it's not guaranteed that one gets all instances where this occurs. The most qualified persons for this job would be the Oracle EF-provider team who wrote the existing "node to sql" translations in the first place. It would already help if some basic linq queries worked, like Invincible's example: entities.Users.Include("UserGroup.ParameterSet.Parameters").


    Let me be frank, I'm a software developer who is paid to write complex applications that use existing components like yours (Oracle DBMS, etc). But it should not be my job to bugfix/enhance the data provider from Oracle or the Entity Framework from Microsoft. If the solution provided by Microsoft/Oracle is so infeasible as right now (where one cannot be sure that linq queries that one writes will actually work) that software developers might not have another option but to choose different solutions that do not have these problems (i.e. EF4+MSSQL or possibly also NHibernate+ORACLE).
  • 7. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    The fact that not a single provider writer in Oracle, MS, IBM, MySQL, DevArt, DataDirect, etc. has been able to solve this problem makes open sourcing ODP.NET provider code unlikely to lead to a solution for APPLY. If the SQL and provider writer experts in the world can't do it, then there's little hope that it is possible at all.

    I agree it shouldn't be your job to fix these type of issues, but Oracle can't change EF's design. We can only provide a solution when a solution is feasible. Technically, this isn't an issue with EF's design, rather it's a problem with LINQ. One can always use entity SQL or straight SQL. Not ideal, but it's a workaround.
  • 8. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated

    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.

  • 9. Re: Entity Framework generates invalid oracle sql.
    ff860565-7e54-4b6b-851c-ad97778be190 Newbie
    Currently Being Moderated

    Hi,

     

    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 11.2.0.2.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.

  • 10. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated

    What is the DB server version at your customer's site and the DB server version for your dev machines?

  • 11. Re: Entity Framework generates invalid oracle sql.
    ff860565-7e54-4b6b-851c-ad97778be190 Newbie
    Currently Being Moderated

    Just checked that and since it is 11.2.0.2.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 11.2.0.2.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?

  • 12. Re: Entity Framework generates invalid oracle sql.
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points