CODEWITHSUNDEEP
c#sql-serverentity-framework-coretemporal-tables
Avrohom Yisroel
Avrohom Yisroel

Reputation: 9470

Renaming a model property causes an error when updating the database with temporal tables

I have an EF Core 6 context that uses temporal tables, and am trying to rename one of the properties on a model.

I have a model that was named PropertyGroupsInvestors, which I renamed to Investment. That worked fine.

I then noticed that the InvestorPayment model had the following...

  public int PropertyGroupsInvestorsID { get; set; }
  public PropertyGroupsInvestors PropertyGroupsInvestors { get; set; } = new();

...which should also have been renamed. I renamed the two properties as follows...

  public int InvestmentsID { get; set; }
  public Investment Investment { get; set; } = new();

...and added a migration.

However, when I tried to update the database, it failed with the error "Setting SYSTEM_VERSIONING to ON failed because table 'MyProject.dbo.InvestorPayments' has 10 columns and table 'MyProject.dbo.InvestorPaymentsHistory' has 9 columns."

Looking in SQL Server Management Studio, I can see that both the InvestorPayments and corresponding history tables have both the old PropertyGroupsInvestorsID column and the new InvestmentId column. They both have 10 columns, so I'm not sure what he message means though. See the screenshot...

enter image description here

Any idea how I fix this? Ideally I need to get rid of the PropertyGroupsInvestorsID column from both tables.

Thanks

Upvotes: 0

Views: 1183

Answers (2)

Faris Džafić
Faris Džafić

Reputation: 59

There is a problem with the renaming of columns in the system versioning table using a code-first approach. So a quick solution (if you don't want to drop temporal tables) can be just to create two migrations. One migration to add a new column, and the second migration to remove the old column.

So first migration will contain both, PropertyGroupsInvestorsID and InvestorsID and then remove PropertyGroupsInvestorsID to create a new migration.

Upvotes: 3

Alv
Alv

Reputation: 21

The same problem here like deleting or modifying one field, it looks like when you have a system-versioned table, you can't modify the structure because it's linked to the history table.

Using SQL server doesn't look like there is any problem: https://learn.microsoft.com/en-us/sql/relational-databases/tables/add-columns-to-a-table-database-engine?view=sql-server-ver15

Upvotes: 1

Related Questions