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

Question

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.

Answer 1

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.

Answer 2

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.