EF Core migrations with suppressTransaction: true fail during publish

[bricelam]

Some SQL statements cannot run inside a transaction. EF Core allows you to suppress the transaction inside the migration:

migrationBuilder.Sql(
    "CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;",
    suppressTransaction: true);

Unfortunately, this doesn't work when applying the migration during publish via Web Deploy.

Error: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

In order to fix this, we need to add transacted="false" to the dbfullsql provider in the MSDeploy manifest.

<dbfullsql path="C:\Path\To\WebApplication1.Data.ApplicationDbContext.sql"
           transacted="false" />

Once we do this, the script will need to start managing transactions. This work is tracked by dotnet/efcore#7681. We may put this feature behind a flag (e.g. --use-transactions) or we may just start always including them in the script.

⚠️ Warning, we need to ensure that we don't disable transactions entirely. Projects referencing older versions of EF Core or users with older versions of dotnet ef won't be able to manage transactions inside the script, so we should never add transacted="false" to the manifest in these cases.

cc @vijayrkn @ajcvickers @JeremyLikness

[bricelam]

(Following up after the implementation of dotnet/efcore#7681)

Any project referencing Microsoft.EntityFrameworkCore version 5.0.0 or higher will include SQL transaction statements (even when using older versions of dotnet ef) and can safely add transacted="false" to the manifest.