使用Oracle.ManagedDataAccess.Core+EF Core 6无法获取TIMESTAMP(6) WITH LOCAL TIME ZONE

使用Oracle.EntityFrameworkCore时，TIMESTAMP WITH LOCAL TIME ZONE类型的列被脚手架自动映射为DateTimeOffset，但执行LINQ查询时抛出System.InvalidCastException异常。Oracle的DATE和TIMESTAMP类型列均可正常读取，仅该类型列报错。尝试移除模型配置中的精度和列类型设置后问题仍存在。

初始查询异常 #

System.InvalidCastException: Specified cast is not valid.
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDateTimeOffset(Int32 i)
at lambda_method332(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.Enumerator.MoveNext() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

相关代码配置 #

表ModelBuilder配置 #

modelBuilder.Entity<TestTable>(entity =>
{
    entity.HasNoKey();

    entity.ToTable("TEST_TABLE");

    entity.Property(e => e.TestColumnDatetime)
                .HasColumnType("DATE")
                .HasColumnName("TEST_COLUMN_DATETIME");

    entity.Property(e => e.TestColumnNumber)
                .HasColumnType("NUMBER")
                .HasColumnName("TEST_COLUMN_NUMBER");

    entity.Property(e => e.TestColumnTimestamp)
                .HasPrecision(6)
                .HasColumnName("TEST_COLUMN_TIMESTAMP");

    entity.Property(e => e.TestColumnTimestampLtz)
                .HasColumnType("TIMESTAMP(6) WITH LOCAL TIME ZONE")
                .HasColumnName("TEST_COLUMN_TIMESTAMP_LTZ");

    entity.Property(e => e.TestColumnVarchar)
                .IsRequired()
                .HasMaxLength(10)
                .IsUnicode(false)
                .HasColumnName("TEST_COLUMN_VARCHAR");
});

实体类定义 #

public partial class TestTable
{
    public decimal TestColumnNumber { get; set; }
    public string TestColumnVarchar { get; set; }
    public DateTime? TestColumnDatetime { get; set; }
    public DateTime? TestColumnTimestamp { get; set; }
    public DateTimeOffset? TestColumnTimestampLtz { get; set; }
}

脚手架生成命令 #

dotnet ef dbcontext scaffold "connection-string" Oracle.EntityFrameworkCore --table TEST_TABLE --context TempContext --context-dir OracleEfCoreData --output-dir OracleEfCoreDomain

补充测试异常（改为DateTime类型） #

将TestColumnTimestampLtz的类型改为DateTime?但保留原ModelBuilder配置时，抛出以下异常：

System.InvalidOperationException: The property 'TestTable.TestColumnTimestampLtz' 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'.
at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidatePropertyMapping(IModel model, IDiagnosticsLogger1 logger) at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger1 logger)
...（省略后续调用栈）

解决步骤 #

1. 添加自定义值转换器 #

Oracle的TIMESTAMP WITH LOCAL TIME ZONE需要通过OracleTimeStampLTZ中间类型转换，在ModelBuilder中为该列配置值转换器：

using Oracle.ManagedDataAccess.Types;

modelBuilder.Entity<TestTable>(entity =>
{
    // 其他属性配置...
    
    entity.Property(e => e.TestColumnTimestampLtz)
        .HasColumnType("TIMESTAMP(6) WITH LOCAL TIME ZONE")
        .HasColumnName("TEST_COLUMN_TIMESTAMP_LTZ")
        .HasConversion(
            // 写入数据库：DateTimeOffset转OracleTimeStampLTZ
            dto => dto.HasValue ? new OracleTimeStampLTZ(dto.Value.UtcDateTime) : null,
            // 读取数据库：OracleTimeStampLTZ转DateTimeOffset
            oracleLtz => oracleLtz.HasValue ? oracleLtz.Value.GetDateTimeOffset() : null
        );
});

2. 保持实体类类型匹配 #

继续使用DateTimeOffset?作为TestColumnTimestampLtz的类型，和值转换器的输入输出类型保持一致。

3. 检查驱动版本兼容性 #

确保Oracle.EntityFrameworkCore和Oracle.ManagedDataAccess.Core版本一致，建议升级到最新稳定版，避免版本差异导致的类型转换问题。

4. 配置连接字符串时区 #

在连接字符串中明确设置时区参数，确保会话时区和数据库时区匹配：

Data Source=你的数据源;User Id=用户名;Password=密码;TimeZone=LOCAL;

内容的提问来源于stack exchange，提问作者Aman Singla