Problem with saving milliseconds via EF with Devart Oracle 9.8.838

[michalkoh]

Hello,

we've upgraded Devart Oracle Data Provider for EF to the latest version (9.8.838.0) and we're currently experiencing an weird issue. Our model entity contaning a property of type DateTime is being saved in the DB without milliseconds part. Date and time (incl. seconds) is saved OK, but milliseconds are always 0.
For the mapping of this property we are using a method HasPrecision() in order to have a column of type TIMESTAMP in our Oracle DB.

Code: Select all

...
Property(e => e.CreatedAt).HasColumnName("CREATED_AT").HasPrecision(6);
...

After the upgrade we realized that the milliseconds for this column are always 0, they are just not saved. We were also able to find out that it has something to do with the option OracleEntityProviderConfig.Instance.CodeFirstOptions.UseDateTimeAsDate which we have set to true (for some unknown historical reasons..). When we set this option to false, saving of the milliseconds works again but we are not completely sure what else could be touched by changing this option.

Anyway also if this option is set to true we are not having this issue in the previous versions of your provider (our stable branche is currently using version 9.4.326.0). Have you changed anything regarding CodeFirstOptions in your latest release?

[Shalex]

Please refer to https://www.devart.com/dotconnect/oracl ... story.html:
9.5.520 24-May-18
* The bug with the DateTime? properties, when config.CodeFirstOptions.UseDateTimeAsDate is set to True, in EF Core is fixed

The corresponding thread on our forum is viewtopic.php?t=36550.

Does this explain the behavior you have encountered?

[michalkoh]

Not really. We are using EF 6.2 not EF Core.

Let's have a sample object Foo:

Code: Select all

public class Foo
{
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; }
}

with its map:

Code: Select all

public class FooMap : EntityTypeConfiguration<Foo>
{
    public FooMap()
    {
        ToTable("FOO", "DEVART_TEST");
        HasKey(e => e.Id);
        Property(e => e.Id).HasColumnName("ID");
        Property(e => e.CreatedAt).HasColumnName("CREATED_AT").HasPrecision(6);
    }
}

Now let's create a DB based on this model. The option UseDateTimeAsDate is set to false at this moment. The table which is created in the Oracle DB is called FOO with 2 columns: ID:NUMBER, CREATED_AT:TIMESTAMP.

If I now create and insert a simple instance of Foo, it is saved properly - with milliseconds.

Code: Select all

using (var context = new DataContext())
{
    var foo = new Foo() { CreatedAt = DateTime.Now };
    context.Foos.Add(foo);
    context.SaveChanges();
}
..
// result in the DB -> ID: 1, CREATED_AT: 2019-10-03 11:58:01.545

Now we change the option UseDateTimeAsDate to true without recreating the DB. The column CREATED_AT is still of type TIMESTAMP. However running the same simple code for adding an instance of Foo, we get in the DB an entry without the milliseconds part.

Code: Select all

// result in the DB with UseDateTimeAsDate = true -> ID: 2, CREATED_AT: 2019-10-03 11:59:34.000

[Shalex]

Please run the code and specify your CREATED_AT values inserted into the database:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="MyDbContextConnectionString" connectionString="User Id=****;Password=****;Server=oracle12c.datasoft.local/orclpdb.datasoft.local;" providerName="Devart.Data.Oracle" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
  <entityFramework>
    <providers>
      <provider invariantName="Devart.Data.Oracle" type="Devart.Data.Oracle.Entity.OracleEntityProviderServices, Devart.Data.Oracle.Entity.EF6, Version=9.8.838.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
  </entityFramework>
</configuration>

Code: Select all

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

            using (var context = new MyDbContext())
            {
                context.Database.Delete();
                context.Database.Create();

                var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
                config.CodeFirstOptions.UseDateTimeAsDate = false;

                var foo = new Foo() { Id = 1, CreatedAt = DateTime.Now };
                context.Foos.Add(foo);
                context.SaveChanges(); // result in the DB -> ID: 1, CREATED_AT: 2019-10-05 07:57:47.229581
            }

            using (var context = new MyDbContext())
            {
                var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
                config.CodeFirstOptions.UseDateTimeAsDate = true;

                var foo = new Foo() { Id = 2, CreatedAt = DateTime.Now };
                context.Foos.Add(foo);
                context.SaveChanges(); // result in the DB -> ID: 2, CREATED_AT: 2019-10-05 07:57:47.280444
            }

        }
    }

    public class Foo
    {
        public int Id { get; set; }
        public DateTime CreatedAt { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public MyDbContext() : base(@"name=MyDbContextConnectionString") { }
        public virtual DbSet<Foo> Foos { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Foo>()
                .HasKey(p => p.Id)
                .ToTable("39389_forum", "DEVART_TEST");
            modelBuilder.Entity<Foo>()
                .Property(e => e.Id).HasColumnName("ID");
            modelBuilder.Entity<Foo>()
                .Property(e => e.CreatedAt).HasColumnName("CREATED_AT").HasPrecision(6);
        }
    }
}

[michalkoh]

Hi Shalex,

thank you for you support.

Although your code is working, I believe that this option (when set to true) is not applied. Or at least in this moment it has no effect. So please do the following. First run your code. Then try to comment out the first using section and run it again. Like this:

Code: Select all

static void Main(string[] args)
{
    var monitor = new Devart.Data.Oracle.OracleMonitor() { IsActive = true };

    //using (var context = new MyDbContext())
    //{
    //    context.Database.Delete();
    //    context.Database.Create();

    //    var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
    //    config.CodeFirstOptions.UseDateTimeAsDate = false;

    //    var foo = new Foo() { Id = 1, CreatedAt = DateTime.Now };
    //    context.Foos.Add(foo);
    //    context.SaveChanges(); // result in the DB -> ID: 1, CREATED_AT: 2019-10-05 07:57:47.229581
    //}

    using (var context = new MyDbContext())
    {
        var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
        config.CodeFirstOptions.UseDateTimeAsDate = true;

        var foo = new Foo() { Id = 2, CreatedAt = DateTime.Now };
        context.Foos.Add(foo);
        context.SaveChanges();
    }
}

The means on the second run, the DB is already there and the option is set to true from the beginning.

Also, please make sure to call an empty DbInitializer in your DbContext, otherwise you'll get an exception on the second run.

Code: Select all

public MyDbContext() : base("OracleDbContext")
{
    Database.SetInitializer<MyDbContext>(null);
    Database.Log = Console.Write;
}

[Shalex]

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

[Shalex]

The investigation is in progress.

As a workaround, please replace (Ctrl-H)
.HasPrecision(6)
with
.HasColumnType("timestamp").HasPrecision(6)
in your fluent mapping.

[michalkoh]

Thank you for your support, your proposed solution is working.
Could you please estimate when it will be fixed and released? I am asking whether we should use this workaround or rather wait a little bit for the official version with the fix.

[Shalex]

We cannot provide a timeframe at the moment. Please use the workaround.