Issue with Timestamp mapping to DateTimeOffset EF Core

[DuelingCats]

I am testing time and date code and encountered an issue when trying to fetch TIMESTAMP WITH LOCAL TIMEZONE oracle column type to DateTimeOffset CLR type. I am working with a database first approach and according to the Oracle documentation, the TIMESTAMP WITH LOCAL TIMEZONE should be mapping to DateTimeOffset. When testing inserting data, it works as expected.

When fetching data at context.TimeTesting.ToList() below, I get an InvalidCastException that looks to be a problem with how DateTimeOffset is fetched. This is using Oracle.EntityFrameworkCore version 7.21.13 package. This might also be related to issue #193

Sample Code:

CREATE TABLE TIME_TESTING
(
  ID NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL
, TIME_LOCAL TIMESTAMP(7) WITH LOCAL TIME ZONE
, DATE_TYPE DATE
, CONSTRAINT TIME_TESTING_PK PRIMARY KEY
  (
    ID
  )
  ENABLE
);

[Table("TIME_TESTING")]
public class TimeTesting
{
    [Key]
    [Column("ID")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; } 

    [Column("TIME_LOCAL")]
    public DateTimeOffset TimeLocal { get; set; }

    [Column("DATE_TYPE")]
    public DateTime DateType { get; set; }
}

string dateMarchTest = "2024-03-10T06:59:48.734Z";
var dateMarchOffset = DateTimeOffset.Parse(dateMarchTest);

var dbContextFactory = _serviceProvider.GetService<IDbContextFactory<SMADbContext>>();
using (var context = dbContextFactory!.CreateDbContext())
{
    TimeTesting marchTest = new TimeTesting()
    {
        TimeLocal = dateMarchOffset,
        DateType = dateMarchOffset.LocalDateTime
    };

    context.Add(marchTest);
    context.SaveChanges();
}

using (var context = dbContextFactory!.CreateDbContext())
{
    var test = context.TimeTesting.ToList();
}

[DuelingCats]

I may have identified the issue. I decompiled the Oracle.ManagedDataAccess DLL and looked at the GetDateTimeOffset(Int32) function that is being called, which I included below (omitted some of the unnecessary code).

The main thing to notice is the expected column type to be OraType.ORA_TIMESTAMP_TZ_DTY (type 181) or OraType.ORA_TIMESTAMP_TZ (type 188). Otherwise, InvalidCastException is thrown.

If you look at the documentation for Oracle Built-in Data Types, you will see that TIMESTAMP WITH LOCAL TIME ZONE is type 231 (OraType.ORA_TIMESTAMP_LTZ_DTY) and is not handled by the GetDateTimeOffset(Int32) within the function.

I would consider this a bug on the Oracle.ManagedDataAccess's part.

public DateTimeOffset GetDateTimeOffset(int i) {
    if (ProviderConfig.m_bTraceLevelPublic)
        Trace.Write(OracleTraceLevel.Public, OracleTraceTag.Entry, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset);
    try
    {
        if (!this.m_bInternalCall && this.IsDBNull(i))
            throw new InvalidCastException(OracleStringResourceManager.GetErrorMesg(ResourceStringConstants.DR_NULL_COL_DATA));
        switch (this.m_readerImpl.m_accessors[i].m_internalType)
        {
        case OraType.ORA_TIMESTAMP_TZ_DTY:
        case OraType.ORA_TIMESTAMP_TZ:
            //ommitted
        default:
            throw new InvalidCastException();
        }
    } catch (Exception ex)
    {
        OracleException.HandleError(OracleTraceLevel.Public, OracleTraceTag.Error, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset, ex);
        throw;
    } finally
    {
        if (ProviderConfig.m_bTraceLevelPublic)
            Trace.Write(OracleTraceLevel.Public, OracleTraceTag.Exit, OracleTraceClassName.OracleDataReader, OracleTraceFuncName.GetDateTimeOffset);
    }
}

[alexkeh]

@DuelingCats Thanks for reporting the issue. I was able to reproduce the problem. I filed bug 36417827 to have an Oracle EF Core team member investigate and fix the issue.

[alexkeh]

@DuelingCats I talked with the Oracle EF Core dev team and they clarified what the problem is. We do have a doc bug that indicates the TIMESTAMP WITH LOCAL TIMEZONE default mapping for scaffolding is DateTimeOffset. That is incorrect. It should be DateTime.

This doc correction has been made and will be appear in future doc versions.

If I now modify all the test case code to use DateTime instead of DateTimeOffset, the app will execute as expected and without an error.

        [Table("TIME_TESTING")]
        public class TimeTesting
        {
            [Key]
            [Column("ID")]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int Id { get; set; }

            [Column("TIME_LOCAL")]
            public DateTime TimeLocal { get; set; }

            [Column("DATE_TYPE")]
            public DateTime DateType { get; set; }
        }

        static void Main(string[] args)
        {
            string dateMarchTest = "2024-03-10T06:59:48.734Z";
            var dateMarchOffset = DateTime.Parse(dateMarchTest);

            using (var db = new TTContext())
            {
                TimeTesting marchTest = new TimeTesting()
                {
                    TimeLocal = dateMarchOffset,
                    DateType = dateMarchOffset
                };

                db.Add(marchTest);
                db.SaveChanges();
            }

            using (var db = new TTContext())
            {
                var test = db.TT.ToList();
            }
        }