Data transfer when adding a one-to-one relationship to EF Core?

Question

After adding a new one-to-one relationship to one of my tables, I cannot figure out how to add default data for existing rows in my database.

My database basically looked like this before the update:

-- Team -- Name: TEXT -- History -- Id: INT 

... where History has foreign keys pointing to it from other unrelated tables.

In my update, I basically want the Team to have one History, so my new db looks like this:

 -- Team -- Name: TEXT HistoryId: INT -- History -- Id: INT 

Now my problem is that I have existing commands in my database, and they need to have unique history lines that need to be referenced, so I somehow need to create a new history line for each existing command.

I tried to manually add entries to the Up-method in my migration, but since my models do not match the existing scheme, this fails.

 protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.AddColumn<int>( name: "HistoryId", table: "Team", nullable: false, defaultValue: 0); using (var db = new XMDBContext()) { foreach (var team in db.Team) team.History = new XMHistory(); db.SaveChanges(); } migrationBuilder.CreateIndex( name: "IX_Team_HistoryId", table: "Team", column: "HistoryId", unique: true); migrationBuilder.AddForeignKey( name: "FK_Team_History_HistoryId", table: "Team", column: "HistoryId", principalTable: "History", principalColumn: "Id", onDelete: ReferentialAction.Cascade); } 

Answer 1

Unfortunately, EF Core does not currently support loading data from migration. The problem is tracked in their repository as # 629 - Seed Data .

The only solution I see at the moment is to use the old good SQL using the MigrationBuilder.Sql method. Unfortunately, there is no access to the db provider services, so the following applies to SQL Server (although I tried to use only standard SQL commands).

Let the original model be as follows:

 public class Team { public int Id { get; set; } public string Name { get; set; } } public class History { public int Id { get; set; } } 

After adding FK from Team to History it becomes:

 public class Team { public int Id { get; set; } public string Name { get; set; } public int HistoryId { get; set; } public History History { get; set; } } 

Automatically generated migration:

 protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.AddColumn<int>( name: "HistoryId", table: "Team", nullable: false, defaultValue: 0); migrationBuilder.CreateIndex( name: "IX_Team_HistoryId", table: "Team", column: "HistoryId"); migrationBuilder.AddForeignKey( name: "FK_Team_History_HistoryId", table: "Team", column: "HistoryId", principalTable: "History", principalColumn: "Id", onDelete: ReferentialAction.Cascade); } 

Now, before the CreateIndex command, we manually insert the following:

 migrationBuilder.AddColumn<int>( name: "TeamId", table: "History", nullable: true); migrationBuilder.Sql(@"insert into History (TeamId) select Id from Team"); migrationBuilder.Sql(@"update Team set HistoryId = (select Id from History where TeamId = Team.Id)"); migrationBuilder.DropColumn( name: "TeamId", table: "History"); 

The idea is simple. We create a temporary column with a TeamId value of TeamId in the History table, insert a new record with the corresponding TeamId for each record in the Team table, and then update the HistoryId column in the Team table, using the TeamId command from the History table as the key and finally delete the temporary column .

At this point, the data conversion is completed and an FK constraint can be created.

Far from good practices, but can be used as a workaround.

Edit: After Hert Arnold's comments , it seems that using SQL blocks is the right way. The only thing that concerns me is how to write database agnostic and / or specific SQL. Of course, the problem does not exist if you are targeting one particular type of database. In any case, if you need to process different types of databases, you can always use standard SQL commands supported by all target databases in combination with specific if blocks based on MigrationBuilder.ActiveProvider .