Entity Framework Core code-first perform additional actions when generating migrations-CodePudding

I want to keep a history of changes made to all of my tables generated by EF Core.

I could do this by manually creating a second model for every model I want duplicated, but I was wondering if there's any way to do this automatically somehow.

What I would want it to do is, every time a migration is generated that includes a CreateTable call, create a duplicate of the table with something like "History" appended to the name, and with an additional column relating to the original record.

I'm not sure what would be the best way to achieve this, as I'm not very experienced with ASP.NET yet. I was thinking of overriding the MigrationBuilder.CreateTable method, but I can't figure out how I'd get the actual migration builder to use my overridden method.

CodePudding user response：

If I understand it correctly, you want to execute custom SQL script before running the migration.

Well, that is indeed possible.

You will generate the migration as you do now and you'll get something like this:

public partial class MyMigration: Migration
            {
                protected override void Up(MigrationBuilder migrationBuilder)
                {
                   //auto-generated mappings
                }
        
            }

before the auto-generate call, you could insert your own SQL script like this:

   public partial class MyMigration: Migration
                {
                    protected override void Up(MigrationBuilder migrationBuilder)
                    {
                        migrationBuilder.Sql("CREATE TABLE Customers_History (CustomerName, City, Country)");

                        migrationBuilder.Sql("INSERT INTO Customers_History (CustomerName, City, Country) SELECT CustomerName, City, Country FROM Customers)");

                       //auto-generated mappings
                    }
        
            }

This is just an example, but you can add your own SQL to match your scenario.

With this solution you are not required to create additional models.

CodePudding user response：

Note that SQL does support Temporal Tables which internally create a second table for you. It has start/end time columns instead of a single column. The database engine maintains this for you (meaning even ad hoc sql queries not through EF are properly tracked into the history table). This is part of the ANSI SQL standard so you might find this easier than rolling your own. You can read more about these on the MSFT documentation page