EF Core: stateful provider across connections

[powelerl]

Hi,

Using dotConnect for Oracle 9.2.

Problem is invalid SQL is executed for Oracle 11 when using SingleOrDefault (FETCH FIRST 2 ROWS ONLY).

I have two database connections in the same program: one connected to Oracle 12 and the other to Oracle 11. If the connection to Oracle 12 is opened first the problem occurs later on when connected to Oracle 11. If Oracle 11 is the first connection opened everything works fine - no problem. Conclusion is that connection order does matter and it seems to me like state is somehow shared across the two connections.

I can control the connection order in development, but that will not be the case in a production environment. As a way to bypass the problem: is it possible to restrict the SQL generation to Oracle 11 SQL syntax?

[Shalex]

Thank you for your report. We will investigate the issue and notify you about the result.

[Shalex]

The config.Workarounds.ProviderManifestToken option is added for setting provider manifest token for all contexts within application domain starting from the 9.2.187 build.

The example code that specifies that target server is Oracle 11.2.0.1:

Code: Select all

var config = OracleEntityProviderConfig.Instance;  
config.Workarounds.ProviderManifestToken = "Oracle, 11.2.0.1";  

[powelerl]

Will this be fixed such that the correct SQL generation scheme is used according to a database connection / database version?

[Shalex]

1. The problem is not provider specific. EF provider doesn't take part in caching commands by EF engine. That's why we recommend you to set ProviderManifestToken to "Oracle, 11.2.0.1" to provide compatible SQL generation for all connections within the same application domain. You can do that in the code or via *.config:

Code: Select all

    <section name="Devart.Data.Oracle.Entity" type="Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfigurationSection, Devart.Data.Oracle.Entity.EFCore" />
  </configSections>
  <Devart.Data.Oracle.Entity xmlns="http://devart.com/schemas/Devart.Data.Oracle.Entity/1.0">
    <Workarounds ProviderManifestToken="Oracle, 11.2.0.1" />
  </Devart.Data.Oracle.Entity>

2. Another workaround you can try.
Assuming, you have MyContext : DbContext. Create additional MyContext11 : MyContext (used for connection to Oracle 11.2 and below) and MyContext12 : MyContext (used for connection to Oracle 12c) with empty class implementations but with the corresponding constructors. Most likely command caching is specific to the context class, so now it should work.

[powelerl]

Somewhere state is being maintained between instatiations of EF contexts. If I understand what you are saying, this is not the data provider maintaining state (dotConnect for Oracle), but rather EF Core itself.

If that's the case I will file a bug report with the EF Core team.

[Shalex]

That is correct. Please contact the EF Core team.

We have checked the behavior of System.Data.SqlClient:

Code: Select all

    var optionsBuilder = new DbContextOptionsBuilder();
    optionsBuilder.UseSqlServer(@"server=dbmssqlx64\mssql2016;initial catalog=test;user id=sa;");
    var context = new ModelContext(optionsBuilder.Options);
    var result = context.Dept.Skip(1).FirstOrDefault(); // success

    var optionsBuilder2 = new DbContextOptionsBuilder();
    optionsBuilder2.UseSqlServer(@"server=dbmssql\mssql2008;initial catalog=test;user id=sa;");
    var context2 = new ModelContext(optionsBuilder2.Options);
    var result2 = context2.Dept.Skip(1).FirstOrDefault(); // error: Incorrect syntax near 'OFFSET'.

The error is thrown because OFFSET FETCH is a new feature added to Sql Server 2012 (and higher), and it is not available in Sql Server 2008.

[powelphu]

I could not find any issue for this on the EFCore github so I created one https://github.com/aspnet/EntityFramewo ... sues/11824.

[powelphu]

So if you look at the GitHub issue ajcvickers seems confident that this is an issue DevArt should fix. So it seems that DevArt either has to fix it inside the provider or make a PR to EntityFramework Core.

Now, please don't send me back to GitHub.. I'm already tired of the "blame game".

[Shalex]

We have reopened the ticket and are investigating it.

[Shalex]

The bug with applying the updated provider behavior after changing connection string options in EF Core 2 is fixed. We will notify you when the corresponding build is available for download.

[Shalex]

dotConnect for Oracle v9.6.540 is released: viewtopic.php?f=1&t=37342.