Clever Geek Handbook

Oracle Entity Framework Provider does not store DateTime.Now with milliseconds

I have basically the same question as this guy.

why can't I save the current DateTime.Now with Entity Framework

But he was using SQL Server and I am using Oracle. (My app needs to work with both)

His problem was that the precision was incorrectly set at the db level.

I noticed that if I manually edit the milliseconds in the oracle database, EF can output the correct timestamp with milliseconds. But when I create an Entity with a DateTime property for "DateTime.Now" it gets truncated.

DateColumn1 attribute is of type Oracle Timestamp

I wrote down the insert statement

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

The crazy thing is, this works in SQL Server.

+3


source to share


2 answers


Aha! An amazing colleague of mine had an idea and it worked!

In our EF code, we tried to put

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

And then DateTime.Now

with milliseconds entered into the database

Update - worth mentioning how I got into this predicament



Building a database using a model First in a test application

  • My application needs to work with both SQL Server and Oracle. So that...
  • I started by designing my database in an EDMX diagram
  • Once the diagram was done, I created a DDL for SQL Server.

  • For some reason, the Oracle EF provider was unable to generate the DDL, so I proceeded to manually modify the SQL Server DDL so that it was syntactically correct

    First problem - my Oracle DDL was using Date instead of Timestamp. Make sure you are using Timestamp !!! DateTime in Oracle does not store milliseconds.

Using First code from the database for the actual solution

  • I wanted the application to take the Code First approach (just my preference. I think it's easier to maintain).
  • So, I connected to a SQL Server database and generated all my classes from this schema.
  • I got all my unit tests and then decided to test it with Oracle Database
  • Even after switching from DATE to Timestamp, I still had problems getting milliseconds input.
  • I generated another Code First model in a typed visual studio solution test TIMESTAMP(6)

    in Oracle, except when I looked at the code OnModelCreating

    , it generated nothing with the help HasPrecision(6)

    and there were no property decorators in the generated C # POCO class.
  • I noticed that if you have the code HasPrecision(6)

    in OnModelCreating

    , Code First CreateDatabase()

    will actually do Oracle TIMESTAMP(6)

    . If you don't, the Oracle EF provider will useDATE

I think that if you take the Model First approach, you can set precision values ​​in an EDMX chart, but I've heard this is bad practice.

+5


source


I wanted to add to the above because it confused me a little and sent me down the wrong path. Date fields should not use 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 when creating the model 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 milliseconds. Time with seconds is recorded by default in DateTime field

+1


source






More articles:


All Articles