EF Core: stateful provider across connections

EF Core: stateful provider across connections

Postby powelerl » Thu 22 Dec 2016 10:14

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?
powelerl
 
Posts: 13
Joined: Thu 22 Dec 2016 10:00

Re: EF Core: stateful provider across connections

Postby Shalex » Thu 22 Dec 2016 20:25

Thank you for your report. We will investigate the issue and notify you about the result.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: EF Core: stateful provider across connections

Postby Shalex » Wed 15 Mar 2017 15:59

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"; 
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: EF Core: stateful provider across connections

Postby powelerl » Thu 20 Apr 2017 11:40

Will this be fixed such that the correct SQL generation scheme is used according to a database connection / database version?
powelerl
 
Posts: 13
Joined: Thu 22 Dec 2016 10:00

Re: EF Core: stateful provider across connections

Postby Shalex » Fri 21 Apr 2017 14:28

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: EF Core: stateful provider across connections

Postby powelerl » Tue 09 May 2017 21:03

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.
powelerl
 
Posts: 13
Joined: Thu 22 Dec 2016 10:00

Re: EF Core: stateful provider across connections

Postby Shalex » Thu 11 May 2017 18:01

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle