dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

[John Liu]

I had everything worked great with EF6 RC1 + dcoracle79Pro. I upgraded to EF6.0.1 today. Everything still works great with the Sql Server Provider. I upgraded my licenses to dcoracle81Pro. It breaks my application. After I migrate my database to the latest version, it gives me the following message when my model is not changed at all
(Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.)

After I use Add_migration command to add a migration,I found out it requires every datatime field in the model to be mapped to a timestamp field instead of a date field as before.
It adds an alter column statement for every one of my datetime fields.
AlterColumn("AZ.ACTIONTOOTHERSYSTEM", "DATETIMEREQUESTED", c => c.DateTime(nullable: false, precision: 7));

I think that should not be the default behavior and it doesn't match with sql server provider, and it's not backward compatible.

we're ready to release our software with EF6.0.1. this is a show stopper.
please help...
thanks
JL

[Shalex]

It adds an alter column statement for every one of my datetime fields.
AlterColumn("AZ.ACTIONTOOTHERSYSTEM", "DATETIMEREQUESTED", c => c.DateTime(nullable: false, precision: 7));

The behaviour was changed since the 7.0.6 (22-May-12) version of dotConnect for Oracle:

The behaviour is changed: by default in Model-First and Code-First approaches the DateTime property is not TIMESTAMP anymore, but TIMESTAMP(7), and the DateTimeOffset property is TIMESTAMP(7) WITH TIME ZONE

We do not know the reason why EF6 RC1 + dcoracle79Pro worked in a different way in your environment.

As a workaround (which works for EF6 only), please use a lightweight convention to map DateTime to Timestamp with a needed precision (X):

Code: Select all

public class MyContext : DbContext {
 
  protected override void OnModelCreating(DbModelBuilder modelBuilder) {
 
    modelBuilder
      .Properties()
      .Where(p => p.PropertyType == typeof(DateTime))
      .Configure(p => p.HasPrecision(X));
  }
 
  // ...
}

[John Liu]

Yes, You're right. The system.Datetime fields were created as Timestamp fields in oracle with EF6 RC1 + dcoracle79Pro. that is not the problem. The problem seems to be a contradiction between the sql server provider (System.Data.SqlClient) and your dotConnect for oracle (8.1.26.6). I use the same code set to manage both sql server and oracle databases. Here are the steps to reproduce the problem.
1. Use EF6.0.1 with the sql server provider to define the migration (V20130301), and update the database to the latest version. Everything works great in sql server environment

2. Switch to an oracle db (11g) using dotConnect for oracle
PM> update-database -verbose
(Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.)
It requires me to add a statement for every datetime field
AlterColumn("AZ.ACTIONTOOTHERSYSTEM", "DATETIMEREQUESTED", c => c.DateTime(nullable: false, precision: 7));

3. PM> add-migration -verbose V20130301
Re-scaffolding migration 'V20130301'.
Only the Designer Code for migration 'V20130301' was re-scaffolded. To re-scaffold the entire migration, use the -Force parameter.
Everything starts to work correctly in oracle environment

4.Switch back to a sql server db using the sql server provider.
PM> update-database -verbose.
(Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.)
It requires me to add a statement for every datetime field
AlterColumn("AZ.ACTIONTOOTHERSYSTEM", "DATETIMEREQUESTED", c => c.DateTime(nullable: false));

This statement doesn't have "precision : 7". it seems to be a contradiction between two providers.Is this what you added to support Oracle 12C?

5. The problem is I can't come up with a migration on datetime fields that would work with sql server and dotConnect for oracle provider.
thanks
JL

[John Liu]

I also applied the suggested workaround. It doesn't solve the problem. It behaves the same. I have no problem to make it work for either sql server or oracle. But I need the same code set to work for both sql server and oracle. dotConnect for oracle 7.9.322.6 worked great for me. Would it work with EF6.0.1? I think the problem is on "precision: 7" you added to support oracle 12C.
thanks
JL

[Shalex]

We have reproduced the issue and are investigating it.

[hoekki]

Got the same Problem. Complete show stopper for us. Is there an estimate on when it will be fixed?

[Shalex]

The bug with generating Code-First Migrations for the DateTime/DateTimeOffset properties in Entity Framework 6 is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New build of dotConnect for Oracle 8.1.45 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=28408.

[hoekki]

Thanks for the quick fix Will test it right away.
Cheers guys!

[John Liu]

Thanks a lot. That fixed the problem.
JL

[hfj]

I seem to have run into this problem as well.

I am using entity framework 6.1.1 and devart.data.oracle.entity 8.4.225.6

My story is a bit different than OP in that I had an Oracle database, and I have now added support for MSSQL (Azure variety)

Whenever I use MSSQL it wants to add migrations for all my DateTimes
e.g

Code: Select all

AlterColumn("k.MyTable", "MyDate", c => c.DateTime());

And when I switch back to Oracle it insists I add another migration
e.g

Code: Select all

AlterColumn("k.MyTable", "MyDate", c => c.DateTime(precision: 7));

And if I do this, it just oscillates between wanting to add/remove precision from the datetime

[Shalex]

The reason of the issue was described at http://forums.devart.com/viewtopic.php?t=28925#p100544.

There are possible alternative solutions:
1) usage of automatic migrations (not code-based)
2) generation of provider-specific code-based migrations