Reverse Engineering Documentation for Oracle DATE refers to a .NET data type that seemingly doesn't exist

[sccunningham7]

https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/EFCoreREDataTypeMapping.html#GUID-4FF3A9A3-AE49-431B-A4FB-31F8C53FCCF5 has the Oracle DATE type as being mapped to the .NET System.Date type. This type does not exist as far as I can tell according to the Microsoft documentation here: https://learn.microsoft.com/en-us/dotnet/api/system?view=net-6.0#structs. I am using .NET 6, EF Core 6.0.5, and Oracle.EntityFrameworkCore 6.21.61.

If I do not explictly declare .HasColumnType("DATE") in my configuration, the provider takes 20x longer to return any results involving a DATE type, implying there is some kind of type conversion happening behind the scenes.

As noted in #248 neither the DateOnly or TimeOnly structs are supported. What is the correct .NET data type that Oracle's DATE is supposed to map to? If this is the wrong documentation, please point me in the right direction.

[alexkeh]

Thanks for pointing out this doc bug. The correct data type is System.DateTime.

We'll fix this in the next doc version publication.

[sccunningham7]

If by default DATE should map to System.DateTime there seems to be an issue with how its translating queries to the database. There is some kind of implicit data type conversion occurring unless you explicitly declare the Oracle datatype, which I am trying to avoid for DB interoperability.

Column type explicitly declared:

builder.Property(p => p.TestDate)
                .HasColumnName("TEST_DATE")
                .HasColumnType("DATE");

EF Execution information:
[11:28:05 INF] Executed DbCommand (17ms) [Parameters=[:p0='2022-11-27T00:00:00.0000000' (Nullable = true) (DbType = Date)], CommandType='Text', CommandTimeout='0']

Column type not explicitly declared:

builder.Property(p => p.TestDate)
                .HasColumnName("TEST_DATE");

EF Execution information:
[11:23:40 INF] Executed DbCommand (18,741ms) [Parameters=[:p0='2022-11-27T00:00:00.0000000' (Nullable = true) (DbType = DateTime)], CommandType='Text', CommandTimeout='0']

Note the DbType in the execution information is different. When DbType = DateTime the execution takes almost 20 seconds. This doesn't seem like intentional behavior if the default mapping is DateTime.

[alexkeh]

What DB server version are you using? Do you have a test case that can reproduce the slow behavior? If not a test case, can you provide the full reverse engineering trace?

[sccunningham7]

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
.NET 6, EF Core 6.0.5, and Oracle.EntityFrameworkCore 6.21.61

I use a custom tool to scaffold things, not the stock reverse engineering, so no trace. You have to actually hit a database to reproduce.

My backing Oracle data is a view containing a DATE column. It has a backing index and is not part of any keys.

Creating a simple entity with a DateTime and then an EF configuration for that entity.

public class TestEntity {
    public DateTime TestDateTime {get; set;}
}

Inside EF DbContext (not the full context)

public virtual DbSet<TestEntity> TestEntity {get; set;}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TestEntity>(entity =>
    {
        // Comment the HasColumnType off and on to see performance difference
        entity.Property(p => p.TestDateTime)
            .HasColumnName("TEST_DATE");
            //.HasColumnType("DATE")
    });
}

The key point here is to swap the HasColumnType fluent API defintion on the entity configuration. This affects how EF goes to the DB. When DATE is not specified is when we get the performance hit. I'm not sure how else I can provide a test case other than creating a full repo minus the DB.

[alexkeh]

New doc version will be published this week.
https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/index.html

Closing this issue.