EF Core+Oracle Code First执行数据库更新时遇ORA-00902错误

问题场景 #

使用EF Core结合Oracle（Code First模式），执行dotnet ef database update命令后触发ORA-00902: invalid datatype错误，初始无法定位无效数据类型。当前Oracle用户/schema为空，这是创建初始迁移后的首次数据库更新操作。

错误日志及生成的SQL #

执行命令时生成的SQL及错误日志如下：

2024-09-27 16:11:34,098 INFO Microsoft.EntityFrameworkCore.Database.Command - Executed DbCommand (73ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE
"RunnerStates" (
    "Id" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL,
    "Name" NVARCHAR2(450) NOT NULL,
    "Description" NVARCHAR2(2000),
    CONSTRAINT "PK_RunnerStates" PRIMARY KEY ("Id")
)';
END;
2024-09-27 16:11:34,121 ERROR Microsoft.EntityFrameworkCore.Database.Command - 2024-09-27 16:11:34.121034 ThreadID:1   (ERROR)   OracleRelationalCommand.ExecuteNonQuery() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902: invalid datatype
ORA-06512: at line 2
   at OracleInternal.ServiceObjects.OracleFailoverMgrImpl.OnError(OracleConnection connection, CallHistoryRecord chr, Object mi, Exception ex, Boolean bTopLevelCall, Boolean& bCanRecordNewCall)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
2024-09-27 16:11:34.121034 ThreadID:1   (ERROR)   OracleRelationalCommand.ExecuteNonQuery() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902: invalid datatype
ORA-06512: at line 2
   at OracleInternal.ServiceObjects.OracleFailoverMgrImpl.OnError(OracleConnection connection, CallHistoryRecord chr, Object mi, Exception ex, Boolean bTopLevelCall, Boolean& bCanRecordNewCall)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
   at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)

涉及的实体类及配置 #

RunnerState实体类 #

public class RunnerState
{
    public int Id { get; set; }

    public required string Name { get; set; }

    public string? Description { get; set; }
}

实体配置代码 #

public class RunnerStateConfiguration : IEntityTypeConfiguration<RunnerState>
{
    public void Configure(EntityTypeBuilder<RunnerState> builder)
    {
        builder
            .HasKey(s => s.Id);
        builder
            .HasIndex(s => s.Name)
            .IsUnique(true);
        builder
            .HasData(
            new RunnerState
            {
                Id = 1,
                Name = "AVAILABLE",
                Description = "The runner is available for receiving and executing assigned tasks.",
            },
            new RunnerState
            {
                Id = 2,
                Name = "UNAVAILABLE",
                Description = "The runner is unavailable for receiving and executing assigned tasks.",
            });
    }
}

排查结果与原因 #

错误并非来自RunnerStates表的创建语句，而是源于项目中另一张表的布尔类型字段：Oracle仅从23c版本开始原生支持BOOLEAN类型，当前使用的是Oracle 18c，EF Core生成的SQL中对该布尔字段使用了BOOLEAN类型，从而触发ORA-00902无效数据类型错误。

内容的提问来源于stack exchange，提问作者Zdeněk