When using SQL server replication when renaming a column the migration uses `sp_rename` which fails on db update.

[sommmen]

What problem are you trying to solve?

Just had the following case:

Replication db setup in /prod/ not in /dev/ environment using mssql.
Applying the migration to /dev/ works but fails for /prod/ with the following message:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot rename the table because it is published for replication.

Describe the solution you'd like

I'd like to be able to mark my context as 'being replicated'.
Then migrations should not use the RenameColumn method in the generated migration.
It should either fault or generate an delete update.

Also a mention of this on the docs pages would be helpful:
https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli#column-renames

[roji]

It should either fault or generate an delete update.

It certainly doesn't seem like a good idea to silently convert a RenameColumn operation to a drop+create, causing the entire data in the column to be lost.

Re faulting - how would that actually be any better than the current situation? You're getting a fault from SQL Server, providing a very decent error explaining the situation - I don't see how EF could do any better.

[sommmen]

It certainly doesn't seem like a good idea to silently convert a RenameColumn operation to a drop+create, causing the entire data in the column to be lost.

It displays a warning on possible data deletion I would assume like in other cases? Then I could edit the migration to fixup the data when using drop+create.

how would that actually be any better than the current situation?

It would then show to me that the created migration would not ever be able to run.

I'm having issues with this because the dev db holds no replica but prod db does, so now during development there are not any issues - then I run my deployment pipeline and we crash on the migration.

My problem is that now code is committed to my main branch expecting the migration to work since it was properly applied to dev and it then crashing anyways.

A way to force drop+create in some way would cause migrations to always work regardless of environment.

It can be said that having the dev db also be replicated would help me catch this during development - but that database is large and I'd like to avoid that.

---

Let me know if this helps - otherwise feel free to close this issue I understand replication dbs are a bit out of scope and I was hesitant to post anything in the first place.

[roji]

It certainly doesn't seem like a good idea to silently convert a RenameColumn operation to a drop+create, causing the entire data in the column to be lost.

It displays a warning on possible data deletion I would assume like in other cases? Then I could edit the migration to fixup the data when using drop+create.

It does, but that warning isn't a blanket permission for us to turn an explicit RenameColumn operation which the user expressed into a very, very destructive operation that drops a column without the user ever having expressed that intent. That's not how we do things.

how would that actually be any better than the current situation?

It would then show to me that the created migration would not ever be able to run.

I'm having issues with this because the dev db holds no replica but prod db does, so now during development there are not any issues - then I run my deployment pipeline and we crash on the migration.

But you've kind of just described why that doesn't make sense: "being replicated" isn't a property of your DbContext, it's a (very possibly temporary) state of some specific database out there. What if you have another prod database that isn't being currently replicated, or you just want to run the migration locally or on some testing/staging database? Also, I don't see how it makes sense to prevent creating such a migration - the migration is legal and correct, and will succeed against databases which aren't being replicated.

What operations exactly are disallowed in this context? You mention RenameColumn above, but the actual SQL Server error seems to reference the table, not the column. Can you point to some clear documentation specifying what's supported and what isn't?

[nithyav2017]

Since EF Core doesn't have a built-in way to detect whether the target SQL Server database is replicated, I was researching possible workarounds. One way is to handle this directly inside the migration by checking the replication state at runtime.

If add a small runtime check inside the migration to detect whether the database is published for replication , and then skip rename operations only when running against a replicated production database. Below is the example code:

protected override void Up(MigrationBuilder migrationBuilder)
{
    if (DatabaseIsReplicated())
    {
        // Skip rename because SQL Server replication forbids it
        return;
    }

    migrationBuilder.RenameColumn("OldName", "NewName", "MyTable");
}

private bool DatabaseIsReplicated()
{
    using var connection = new SqlConnection(_connectionString);
    connection.Open();

    using var command = connection.CreateCommand();
    command.CommandText = @"
        SELECT TOP 1 is_published
        FROM sys.databases
        WHERE name = DB_NAME();
    ";

    var result = command.ExecuteScalar();
    return result != null && result != DBNull.Value && (int)result == 1;
}

[roji]

@nithyav2017 you can't just skip a migration; as far as EF considered, the migration has been applied even though nothing actually happened in the database. For example, the next time you execute a query with EF, it will attempt to read the NewName, and that query is failed because the migration was skipped and the column is still called OldName in the database.

You really have to think about the overall end-to-end behavior you're expecting here. If SQL Server doesn't support certain migration operations while in this state, then you can't have migrations performing those operations. I'm not familiar with SQL Server replication and exact what constraints it imposes - that's why I asked for more details on this above (is it only renames? Of columns, of tables?). Providing more details might help us understand the scope of the problem and think of what EF could do to possibly help.