Update-database 0 doesn't work

[AramisIT]

Let's say I created an initial migration and successfully run Update-Database, everything is great. If I run Update-Database 0 - I'll get the error: "Drop table operation failed on table MyTable because it is not a supported operation on system-versioned temporal tables"

the same error I'll get if I remove some table, create a new migration and run Update-database

In general - I can't remove added tables

[KarlJorgen]

I have the same issue when I need to drop already created tables.

[findulov]

Sorry for being away for so long, this seems to be SQL Server related and I need to research the scenario but it seems the only solution would be just like @KarlJorgen mentioned - to automatically drop all history tables.

[bcallaghan-et]

I tried to workaround this issue by writing the "Down" migration myself. To delete a temporal table in SQL Server, you have to execute the following command first:

ALTER TABLE [Schema].[Table] SET ( SYSTEM_VERSIONING = OFF  )

This disables the history tracking and converts the History table to a regular table. Then you can drop both tables as normal. However, this failed to work with Migrations. I attempted the following (manually written) migration

migrationBuilder.Sql("ALTER TABLE [Schema].[Table] SET ( SYSTEM_VERSIONING = OFF  )");

migrationBuilder.DropTable(
    name: "Table",
    schema: "Schema");

migrationBuilder.DropTable(
    name: "Table",
    schema: "Schema");

which produced the following error message:

Cannot find the object "Schema.Table" because it does not exist or you do not have permissions.

Note that the tables were in fact deleted, and the migration was not rolled back.

Looking through the console output, I see that the library tried to re-enable SYSTEM_VERSIONING for the deleted table, using the same SQL that is normally used to create the temporal table. This is due to the migrator running the default migrations and then running the hard-coded ALTER TABLE statements.

My recommendation is to better incorporate temporal tables into the migration process, as I previously suggested in #13. I've just started using this library (and temporal tables in general), and it's already been useful in my own projects. Let me know if you need anymore information in addressing this issue.

[findulov]

Hey guys,

I've tried to reproduce your problem and managed to find a solution for it. I've extended the existing SqlServerMigrationsGenerator and intercept the DropTableOperation SQL generation and just before "DROP TABLE" command , I've added the code to drop the specified temporal table.

It's all here in this commit: https://github.com/findulov/EntityFrameworkCore.TemporalTables/commit/3dde7f94c5b361116f4a4237fe21c9fec50f7a8d

Please notice that I've added additional safety checks to verify that the base table ACTUALLY exists before attempting to create and drop the corresponding history table.

Having that taken place, I've tried Update-Database 0 and it works now.

Please let me know how it goes at your end.

[qsdfplkj]

I have 2 migrations one to CreateTables and AddTemporalTables. Update-Database works but Update-Database CreateTables (in order to revert the AddTemporalTables) does not work. (There are no errors in the console?)

[findulov]

Temporal table configuration is handled by extensions methods UseTemporalTable/PreventTemporalTable methods and is not related to the migrations. In other words, if you want to make a table temporal, just configure your DbContext like described here https://github.com/findulov/EntityFrameworkCore.TemporalTables#dbcontext-configuration, you don't have to create a new migration specifically for that.