Oracle.EntityFrameworkCore CLR Type mapping issue with Timestamp With Local Time Zone

[fultke01]

Using the current latest Oracle.EntityFrameworkCore (5.21.3), I think there's a bug using TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle.ManagedDataAccess expects to use CLR type DateTime, but Oracle.EntityFrameworkCore requires DateTimeOffset. I think DateTime with kind set to Local is the most appropriate CLR type match.

If I use DateTime, I get the following error when creating code first migrations or querying.

The property 'Event.OccurredAt' is of type 'DateTime' which is not supported by the current database provider. 
Either change the property CLR type, or ignore the property using the '[NotMapped]' attribute or by using 
'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

If I use DateTimeOffset, I can create code first migrations, but I get the following error on query.

System.InvalidCastException
  HResult=0x80004002
  Message=Specified cast is not valid.
  Source=Oracle.ManagedDataAccess
  StackTrace:
   at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDateTimeOffset(Int32 i)
   ...

Here's a sample program:

class Program
{
    public static void Main()
    {
        var id = CreateEvent("TestStarted");
        Console.WriteLine($"Event saved with ID: {id}");

        var @event = GetEvent(id);
        Console.WriteLine($"Event fetched:\r\n{JsonSerializer.Serialize(@event)}");

        Console.WriteLine("Test complete...");
    }

    private static Guid CreateEvent(string eventName)
    {
        var db = new MyContext();

        var @event = new Event(eventName);
        db.Events.Add(@event);
        db.SaveChanges();

        return @event.Id;
    }

    private static Event GetEvent(Guid id)
    {
        var db = new MyContext();

        return db.Events.Find(id); //InvalidCast occurs here with DateTimeOffset
    }
}

public class Event
{
    public Event(string eventName) => EventName = eventName;

    public Guid Id { get; private set; } = Guid.NewGuid();
    public string EventName { get; private set; }
    public DateTimeOffset OccurredAt { get; private set; } = DateTimeOffset.Now;
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseOracle("sandbox connection string");
    }

    public DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>().Property(x => x.Id).HasColumnType("RAW(16)").IsRequired().ValueGeneratedNever();
        modelBuilder.Entity<Event>().Property(x => x.EventName).HasColumnType("VARCHAR2").HasMaxLength(100).IsRequired().ValueGeneratedNever();
        modelBuilder.Entity<Event>().Property(x => x.OccurredAt).HasColumnType("TIMESTAMP WITH LOCAL TIME ZONE").IsRequired().ValueGeneratedNever();
        modelBuilder.Entity<Event>().HasKey(x => x.Id);
    }
}

[fultke01]

I'm not sure if there's a better way to do this, but for now my work around is to use DateTime and override the OracleTypeMappingSource.

public class CustomOracleTypeMappingSource : OracleTypeMappingSource
{
    public CustomOracleTypeMappingSource(TypeMappingSourceDependencies dependencies, RelationalTypeMappingSourceDependencies relationalDependencies, IOracleOptions oracleOptions) 
        : base(dependencies, relationalDependencies, oracleOptions)
    {
    }

    protected override RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
    {
        if (mappingInfo.ClrType == typeof(DateTime) && (mappingInfo.StoreTypeName ?? string.Empty).StartsWith("TIMESTAMP WITH LOCAL TIME ZONE"))
            return new OracleDateTimeTypeMapping(mappingInfo.StoreTypeName, default(DbType?));

        return base.FindMapping(mappingInfo);
    }
}

And then in MyContext, add the following to OnConfiguration

optionsBuilder.ReplaceService<IRelationalTypeMappingSource, CustomOracleTypeMappingSource>();

[alexkeh]

In EF Core migrations, the default mapping from .NET DateTimeOffset is to TIMESTAMP WITH TIME ZONE. .NET DateTime maps to TIMESTAMP by default. Yes, you would need to override the default mapping to use another data type.

[fultke01]

Understood those are reasonable defaults for the two CLR types. But it should be easier than this to use the non-default TIMESTAMP WITH LOCAL TIME ZONE column type with EF Core. My solution to add the additional mapping has Oracle dependencies marked Internal, and isn't a good permanent solution.