EF Core: stateful provider across connections
EF Core: stateful provider across connections
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?
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?
Re: EF Core: stateful provider across connections
Thank you for your report. We will investigate the issue and notify you about the result.
Re: EF Core: stateful provider across connections
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:
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";
Re: EF Core: stateful provider across connections
Will this be fixed such that the correct SQL generation scheme is used according to a database connection / database version?
Re: EF Core: stateful provider across connections
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:
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.
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>
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.
Re: EF Core: stateful provider across connections
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.
If that's the case I will file a bug report with the EF Core team.
Re: EF Core: stateful provider across connections
That is correct. Please contact the EF Core team.
We have checked the behavior of System.Data.SqlClient: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.
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'.
Re: EF Core: stateful provider across connections
I could not find any issue for this on the EFCore github so I created one https://github.com/aspnet/EntityFramewo ... sues/11824.
Re: EF Core: stateful provider across connections
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".
Now, please don't send me back to GitHub.. I'm already tired of the "blame game".
Re: EF Core: stateful provider across connections
We have reopened the ticket and are investigating it.
Re: EF Core: stateful provider across connections
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.
Re: EF Core: stateful provider across connections
dotConnect for Oracle v9.6.540 is released: viewtopic.php?f=1&t=37342.