Missing 'EXECUTE IMMEDIATE' for idempotent migration script

[tommei]

"Microsoft.EntityFrameworkCore.Tools" Version="6.0.35"
"Oracle.EntityFrameworkCore" Version="6.21.160"

Most of the scripts are wrapped with EXECUTE IMMEDIATE but e.g. 'CREATE OR REPLACE TRIGGER' is not. How can I fix it in ef core 6?

The trigger is generated for ConcurrencyToken

Config:

        builder.Property(x => x.Timestamp)
            .HasMaxLength(8)
            .IsConcurrencyToken();

[alexkeh]

I used the .IsConcurrencyToken() in both .NET 6 and .NET 8 with Oracle EF Core 6.21.160 and 8.23.60, respectively. I didn't see any errors occur during the update-database process.

None of the scripts are creating triggers either. What DB server version are you using? Can you provide a complete test case that shows the problem?

[tommei]

Hi, I found why the trigger is generated.
It is generated when this flag is set to true:

I'm not sure why it is set to true. In our case this migration was generated long time ago with ef core 5. Now we just need to be able to script the migration.

Example migration:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Entities",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "RAW(16)", nullable: false),
                    Timestamp = table.Column<byte[]>(type: "RAW(2000)", rowVersion: true, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Entities", x => x.Id);
                });
        }

Example script:

DECLARE
V_COUNT INTEGER;
BEGIN
SELECT COUNT(TABLE_NAME) INTO V_COUNT from USER_TABLES where TABLE_NAME = '__EFMigrationsHistory';
IF V_COUNT = 0 THEN
Begin
BEGIN 
EXECUTE IMMEDIATE 'CREATE TABLE 
"__EFMigrationsHistory" (
    "MigrationId" NVARCHAR2(150) NOT NULL,
    "ProductVersion" NVARCHAR2(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
)';
END;

End;

END IF;
EXCEPTION
WHEN OTHERS THEN
    IF(SQLCODE != -942)THEN
        RAISE;
    END IF;
END;
/

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20241014104347_Initial';
IF v_Count = 0 THEN

    BEGIN 
    EXECUTE IMMEDIATE 'CREATE TABLE 
    "Entities" (
        "Id" RAW(16) NOT NULL,
        "Timestamp" RAW(2000) NOT NULL,
        CONSTRAINT "PK_Entities" PRIMARY KEY ("Id")
    )';
    END;
 END IF;
END;

/

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20241014104347_Initial';
IF v_Count = 0 THEN

    CREATE OR REPLACE TRIGGER "rowversion_Entities"
    BEFORE INSERT OR UPDATE ON "Entities"
    FOR EACH ROW
    BEGIN
      :NEW."Timestamp" := UTL_RAW.CAST_FROM_BINARY_INTEGER(UTL_RAW.CAST_TO_BINARY_INTEGER(NVL(:OLD."Timestamp", '00000000')) + 1);
    END;
 END IF;
END;

/

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20241014104347_Initial';
IF v_Count = 0 THEN

    EXECUTE IMMEDIATE '
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES (N''20241014104347_Initial'', N''6.0.35'')
    ';
 END IF;
END;

/

[alexkeh]

Can you share the complete test case, the DB version you are using, and the verbose trace when your scripts update the DB? Debugging is easier with a test case as EF Core auto-generates a lot of the code.

[tommei]

Hi,

Db version:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

I cant run the script because of syntax error:

I need to manually add 'Execute Immediate' in this case.

My code example:
https://we.tl/t-BY0JunII6T

[tommei]

I generate the script with: Script-Migration -Idempotent

https://learn.microsoft.com/en-us/ef/core/cli/powershell#script-migration

[alexkeh]

I was able to reproduce the issue. I've filed bug 37181655 to have one of the dev team members review the problem.

[alexkeh]

@tommei Can you provide the manually updated script that works without error that you expect ODP.NET EF Core to generate?

[tommei]

sure,

DECLARE
    v_Count INTEGER;
BEGIN
SELECT COUNT(*) INTO v_Count FROM "__EFMigrationsHistory" WHERE "MigrationId" = N'20241014104347_Initial';
IF v_Count = 0 THEN

    EXECUTE IMMEDIATE '
    CREATE OR REPLACE TRIGGER "rowversion_Entities"
    BEFORE INSERT OR UPDATE ON "Entities"
    FOR EACH ROW
    BEGIN
      :NEW."Timestamp" := UTL_RAW.CAST_FROM_BINARY_INTEGER(UTL_RAW.CAST_TO_BINARY_INTEGER(NVL(:OLD."Timestamp", ''00000000'')) + 1);
    END;
    ';
 END IF;
END;

Diff: