.NET8升级后Oracle EntityFramework布尔类型处理异常问题

问题背景 #

从.NET 7升级到.NET 8，同步将相关微软包更新至8.0.7版本，涉及的NuGet包如下：

<PackageReference Include="EntityFrameworkCore.Exceptions.Oracle" Version="8.1.3" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.JwtBearer" Version="8.0.7" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.OpenIdConnect" Version="8.0.7" />
<PackageReference Include="Microsoft.Extensions.Diagnostics.HealthChecks.EntityFrameworkCore" Version="8.0.7" />
<PackageReference Include="Oracle.EntityFrameworkCore" Version="8.23.50" />

问题现象 #

1. 查询生成SQL报错
   升级前可正常运行的查询代码：

[HttpGet("CanDelete/{id:long}")]
public async Task<ActionResult<bool>> CanDelete(long id)
{
     var inUse = await _dbContext.CompositeFunds.AnyAsync(x => x.FundId == id);    
     return Ok(!inUse);
}

升级后EF Core生成的SQL使用了True/False关键字，触发Oracle报错ORA-00904: "FALSE": 无效标识符：

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM "PERFORMANCE"."COMPOSITE_FUND" "c"
        WHERE "c"."FUND" = :id_0) THEN True
    ELSE False
END FROM DUAL

2. 实体布尔属性配置失效
   原先无需指定列类型即可正常工作的配置：

builder.Property(e => e.Active).HasColumnName("ACTIVE").IsRequired().HasDefaultValue(0);

升级后必须添加HasColumnType("NUMBER")才能正常映射：

builder.Property(e => e.Active).HasColumnName("ACTIVE").IsRequired().HasDefaultValue(0).HasColumnType("NUMBER");

解决方案 #

方案1：降级Oracle.EntityFrameworkCore版本 #

将Oracle.EntityFrameworkCore降级至8.21.150版本后，EF Core生成的SQL会自动用1/0替代True/False，恢复正常执行：

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM "PERFORMANCE"."COMPOSITE_FUND" "c"
        WHERE "c"."FUND" = :item_FundId_0) THEN 1
    ELSE 0
END FROM DUAL

方案2：全局配置布尔类型映射 #

在DbContext中重写ConfigureConventions方法，全局指定布尔类型对应的数据库列类型为NUMBER：

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<bool>().HasColumnType("NUMBER");
}

内容的提问来源于stack exchange，提问作者Martin Andersen