CODEWITHSUNDEEP
c#oracle-databaseentity-frameworkdatetime
C. Tewalt
C. Tewalt

Reputation: 2509

Oracle Entity Framework provider doesn't store DateTime.Now with milliseconds

I have basically the exact same question as this guy here.

why i can't save the current DateTime.Now using Entity Framework

But he was using SQL Server, and I am using Oracle. (My application must work with both)

His problem was that precision wasn't set correctly at the db level.

I've noticed that if I manually edit the milliseconds in my oracle database, EF can pull out the correct timestamp with milliseconds. But when I create an Entity with a DateTime property to "DateTime.Now" it gets truncated.

The DateColumn1 attribute is of the Oracle type Timestamp

I logged the insert statement

insert into "SchemaName"."TableName"("DateColumn1") values (:P0) --:P0:'5/14/2015 4:07:27 PM' (Type = Date)

The crazy thing is that this works in SQL Server.

Upvotes: 6

Views: 5746

Answers (3)

D. Pesc.
D. Pesc.

Reputation: 195

Another possible solution is to set precision in the configuration class. If you have an Entity like this:

public class MyEntity
    {
        public DateTime? MyDateTimeWithPrecision{ get; set; }
    }

If you use configuration to be added on model builder as following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
         modelBuilder.Configurations.Add(new MyEntityConfiguration());
        }

then in your configuration class you can do as following:

class MyEntityConfiguration : EntityTypeConfiguration<MyEntity>
    {
        public MyEntityConfiguration()
        {
            Property(e => e.MyDateTimeWithPrecision)
                .HasPrecision(6);
         }
     }

With this architectural solution you can have a MyEntityConfiguration class for each entity of your model, so your code should be more readable.

Upvotes: 2

David Wilton
David Wilton

Reputation: 394

I wanted to add to the above because a bit of this confused me and sent me down the wrong track. The date fields shouldn't use the TIMESTAMP annotation as I tried to do. The fields in your POCO class should remain as DateTime:

   public class TimeClass
    {
        public DateTime? StartTime { get; set; }
        public DateTime? StopTime { get; set; }
    }

Then in on model creating for each of the Date fields in each of your Model Classes you must set the precision of the fields

     protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("MySchema"); //see [here][2]

        modelBuilder.Entity<TimeClass>().Property(p => p.StartTime).HasPrecision(6);
        modelBuilder.Entity<TimeClass>().Property(p => p.StopTime).HasPrecision(6);

    }

Then you can get the milliseconds. Time with seconds are recorded by default with the DateTime field

Upvotes: 1

C. Tewalt
C. Tewalt

Reputation: 2509

Aha! My awesome colleague had an idea and it worked!

In our EF code we tried putting

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<EntityClass>().Property(p => p.TIMESTAMP).HasPrecision(6);
}

And then the DateTime.Now with milliseconds got stored into the database

Update - it's worth mentioning how I got in this predicament

Building the Database with Model First in a "test" application

  1. My app has to work with both SQL Server and Oracle. So...
  2. I started by designing my database in an EDMX Diagram
  3. Once the diagram was done, I generated the DDL for SQL Server.

  4. For some reason the Oracle EF provider couldn't generate the DDL so I proceeded to manually make changes to the SQL Server DDL so it would be correct syntactically

    1st Problem - my Oracle DDL was using a Date instead of Timestamp. Make sure you use Timestamp!!! DateTime in Oracle doesn't store milliseconds.

Using Code First from Database for the actual solution

  1. I wanted the app to use the Code First approach (Just my preference. I think it's easier to maintain)
  2. So I connected to the SQL Server database and generated all of my classes from that schema.
  3. I got all of my unit tests passing and then decided to test it with the Oracle database
  4. Even after changing from DATE to Timestamp, I was still having problems with the milliseconds going in.
  5. I generated another Code First model in a test visual studio solution with a TIMESTAMP(6) type in Oracle, except when I looked at the OnModelCreating code, it did not generate anything with HasPrecision(6) nor were there any decorators on the property in the generated C# POCO class.
  6. I noticed if you have the HasPrecision(6) code in your OnModelCreating, the Code FirstCreateDatabase() will actually make an Oracle TIMESTAMP(6). If you don't, then the Oracle EF provider will use DATE

I think if you do the Model First approach you can set precision values in the EDMX diagram, but I've heard that it's bad practice.

Upvotes: 9

Related Questions