EF Core+Oracle添加检查约束生成无效SQL问题咨询

问题场景 #

使用最新版EF Core搭配Oracle数据库提供程序，尝试通过Fluent API为Person实体的email列添加检查约束，代码如下：

public void Configure(EntityTypeBuilder<Person> builder)
{
    builder.ToTable(t => t.HasCheckConstraint("Osoba_CHk_Email", "email like '_%@_%._%'"));      
}

执行迁移时触发Oracle语法错误：

ORA-06550: line 10, column 53:
PLS-00103: "_" symbol found in a situation where one of the following options was assumed:
& = - + ; < / > at v is mod remainder not rem return
returning <exponent (**)> <> or != or ~= >= <= <> and or like
like2 like4 likec between into using || multiset bulk member
submultiset

问题分析 #

自动生成的迁移SQL存在语法问题：嵌套在EXECUTE IMMEDIATE中的CREATE TABLE语句里，检查约束的like条件单引号未正确转义。生成的错误SQL片段如下：

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE
         "Osoba" (
              "email" NVARCHAR2(50) NOT NULL,
              CONSTRAINT "Osoba_CHk_Email" CHECK (email like '_%@_%._%')
)';
END;

由于EXECUTE IMMEDIATE使用单引号包裹SQL语句，like条件中的单引号会被Oracle误判为字符串结束符，直接导致语法解析失败。

无需修改迁移的解决方案 #

遵循Oracle字符串嵌套时的单引号转义规则——用两个连续单引号表示一个实际单引号，修改EF Core的Fluent API配置代码：

public void Configure(EntityTypeBuilder<Person> builder)
{
    builder.ToTable(t => t.HasCheckConstraint("Osoba_CHk_Email", "email like ''_%@''_%._%''"));      
}

修改后生成的迁移SQL中，检查约束部分会变为CHECK (email like ''_%@''_%._%'')，Oracle解析时会自动将两个连续单引号识别为一个单引号，从而正确执行CREATE TABLE语句，完全无需手动修改自动生成的迁移文件。

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