Migrating data when adding one-to-one relationship in EF Core?

Question

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

My database basically looked like this before upgrade:

-- Team --
  Name: TEXT

-- History --
  Id: INT

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

In my upgrade I basically want a Team to have a single History so my new db looks like:

-- Team --
  Name: TEXT
  HistoryId: INT

-- History --
  Id: INT

My problem now, however is that I have existing Teams in my DB and they need to have unique History rows to point to, so I somehow need to create a new history row for each existing Team.

I tried to manually add the entries in the Up-method in my migration, but since my models don't match the existing schema, 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 currently EF Core does not support seeding data from migration. The issue is tracked in their repository as #629 - Seed Data.

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

Let the original model was 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; }
}

The auto generated migration is:

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 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 nullable column TeamId in the History table, insert a new record with corresponding TeamId for each record in Team table, then update the HistoryId column in the Team table using the TeamId column from the History table as a key, and finally delete the temporary column.

At this point the data transformation is complete and the FK constraint can be created.

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

Edit: After Gert Arnold's comments, looks like using SQL blocks is the right way to go. The only thing that concerns me is how to write database agnostic and/or specific SQLs. Of course the problem does not exist if one is targeting a single specific database type. Anyway, if there is a need to handle different database types, one can always use standard SQL commands supported by all target databases combined with specific if blocks based on MigrationBuilder.ActiveProvider property.