Internal `PRAGMA foreign_check = 0;` commands for SQLite migrations are triggering `NonTransactionalMigrationOperationWarning`

[ferferga]

Bug description

For some operations that require rebuilding tables, the SQLite provider adds a PRAGMA foreign_keys = 0 before each transaction and enables it back after committing. However, this triggers the following warning:

The migration operation 'PRAGMA foreign_keys = 0;' from migration 'x' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it can be applied again. Create a separate migration that contains just this operation.

Although technically true, in my opinion this is confusing and that specific warning should not be issued for that specific operation, because it's introduced internally by EFCore without the knowledge of the user. I assume this warning was added in EFCore 9 to warn possible misuses from users in other situations but didn't take into account this special case.

There are 2 ways to fix this issue from what I've gathered:

• Add an exception here, so any PRAGMA command when using SQLite provider is skipped.
• Switch to https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys, which can be used inside transactions (my PR for this is up at Use PRAGMA defer_foreign_keys instead of PRAGMA foreign_keys when migrating #35873)

I attached the SQL script generated by dotnet script from my project (Jellyfin): script.txt

Your code

Example of a migration that triggers this warning:

Details

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Jellyfin.Server.Implementations.Migrations
{
    /// <inheritdoc />
    public partial class MakeStartEndDateNullable : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<DateTime>(
                name: "StartDate",
                table: "BaseItems",
                type: "TEXT",
                nullable: true,
                oldClrType: typeof(DateTime),
                oldType: "TEXT");

            migrationBuilder.AlterColumn<DateTime>(
                name: "EndDate",
                table: "BaseItems",
                type: "TEXT",
                nullable: true,
                oldClrType: typeof(DateTime),
                oldType: "TEXT");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AlterColumn<DateTime>(
                name: "StartDate",
                table: "BaseItems",
                type: "TEXT",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified),
                oldClrType: typeof(DateTime),
                oldType: "TEXT",
                oldNullable: true);

            migrationBuilder.AlterColumn<DateTime>(
                name: "EndDate",
                table: "BaseItems",
                type: "TEXT",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified),
                oldClrType: typeof(DateTime),
                oldType: "TEXT",
                oldNullable: true);
        }
    }
}

EF Core version

9.0.3

Database provider

Microsoft.EntityFrameworkCore.Sqlite

Target framework

No response

Operating system

No response

IDE

No response