How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
mseckst
Posts: 2
Joined: Tue 15 Apr 2014 11:17

How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by mseckst » Tue 15 Apr 2014 11:28

Hi all,

I was successfully running EntityFramework 5 with dotConnect 7.7 against our Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

Now I upgraded to Entity Framework 6.1 and dotConnect 8.3.115.6. Now many of the generated statements contain things like

Code: Select all

FETCH FIRST 1 ROWS ONLY
instead of

Code: Select all

where rownum <= 1
which I believe are only supported in Oracle 12. Where is the switch to tell dotConnect that it should generate statements compatible with Oracle 11?

best regards,
Markus

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

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by Shalex » Wed 16 Apr 2014 16:29

Please replace ProviderManifestToken="Oracle, 12.1.0.1" with ProviderManifestToken="Oracle, 11.2.0.1" in your model via interface of Entity Developer (*.edml) or via XML Editor (*.edmx).

mseckst
Posts: 2
Joined: Tue 15 Apr 2014 11:17

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by mseckst » Wed 23 Apr 2014 09:23

Thank you, that solved my problem! For some unknown reason I had only "Oracle" as ProviderManifestToken, without any version. Obviously this defaults to 12...

jsalice
Posts: 2
Joined: Wed 23 Apr 2014 11:09

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by jsalice » Wed 23 Apr 2014 11:12

Hi,

I have the same problem but in Code First.
¿How can I set the 'ProviderManifestToken' is code first?

Thanks

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

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by Shalex » Fri 25 Apr 2014 14:15

jsalice wrote:I have the same problem but in Code First.
¿How can I set the 'ProviderManifestToken' is code first?
You are using DbContext template with Fluent Mapping=True (instead of DbContext template with XML mapping), aren't you?

In case of fluent mapping, our provider determines the version of target Oracle server basing on the OracleConnection.ServerVersion property. Please specify its value in your case.

jsalice
Posts: 2
Joined: Wed 23 Apr 2014 11:09

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by jsalice » Mon 28 Apr 2014 10:48

Hi Shalex,

I want to upload my test solution, Is it possible? How? (I don't see an "Upload" button)
I'm using EntityTypeConfiguration<T>.
I found two problems and I guess that the problems are with multiple Include().

The return value of ServerVersion property is: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP and Data Mining options.

When multiple Include() are used an exception is thrown:
Message: OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use.
It's seems that the "DbContext" is generating SQL statements for Oracle 12c instead for Oracle 11g

Multiple Include() has some problems with "boolean" properties in entities
The statement generate a System.FormatException wich it is NOT thown and the boolean property it is NOT set to the correct value (it's always False)

Thanks
Javier

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

Re: How to set the Oracle Version? EF6 is generating statements valid for Oracle 12 only

Post by Shalex » Tue 29 Apr 2014 13:53

jsalice wrote:When multiple Include() are used an exception is thrown:
Message: OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use.
It's seems that the "DbContext" is generating SQL statements for Oracle 12c instead for Oracle 11g
The OUTER APPLY/CROSS APPLY constructions are supported by Oracle server starting from the 12c version. Our implementation in dotConnect for Oracle avoids generation of OUTER APPLY/CROSS APPLY if it is possible, but some LINQ queries cannot be translated without employing OUTER APPLY/CROSS APPLY. In this case you should rewrite your LINQ statement.
jsalice wrote:Multiple Include() has some problems with "boolean" properties in entities
The statement generate a System.FormatException wich it is NOT thown and the boolean property it is NOT set to the correct value (it's always False)
Please send us via http://www.devart.com/company/contactform.html a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

Post Reply