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

[mseckst]

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]

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]

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]

Hi,

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

Thanks

[Shalex]

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]

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]

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.

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.