dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
John Liu
Posts: 61
Joined: Wed 14 Nov 2012 20:58

dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by John Liu » Wed 06 Nov 2013 18:14

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by Shalex » Tue 12 Nov 2013 15:07

John Liu wrote: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
Posts: 61
Joined: Wed 14 Nov 2012 20:58

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by John Liu » Tue 12 Nov 2013 16:04

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
Posts: 61
Joined: Wed 14 Nov 2012 20:58

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by John Liu » Tue 12 Nov 2013 16:48

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by Shalex » Fri 15 Nov 2013 12:39

We have reproduced the issue and are investigating it.

hoekki
Posts: 8
Joined: Thu 21 Nov 2013 14:30

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by hoekki » Tue 26 Nov 2013 09:04

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

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by Shalex » Wed 27 Nov 2013 09:33

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by Shalex » Thu 28 Nov 2013 14:30

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
Posts: 8
Joined: Thu 21 Nov 2013 14:30

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by hoekki » Fri 29 Nov 2013 13:10

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

John Liu
Posts: 61
Joined: Wed 14 Nov 2012 20:58

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by John Liu » Thu 12 Dec 2013 22:59

Thanks a lot. That fixed the problem.
JL

hfj
Posts: 3
Joined: Tue 22 Jan 2013 14:01

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by hfj » Fri 13 Mar 2015 11:47

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
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle 8.1 + EF6.0.1 DateTiem field problems

Post by Shalex » Fri 13 Mar 2015 14:12

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

Post Reply