在 EF Core 中添加一对一关系时迁移数据?

问题描述：

在向我的表中的一个添加新的一对一关系后，我无法弄清楚如何为数据库中的现有行添加默认数据.

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

... History 所在的位置有来自其他不相关表的外键指向它.

... 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.

我尝试在迁移中手动添加 Up 方法中的条目，但由于我的模型与现有架构不匹配，因此失败.

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);
}

答

很遗憾，目前 EF Core 不支持从迁移中播种数据.该问题在他们的存储库中被跟踪为 #629 - 种子数据.

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

我目前看到的唯一解决方案是通过 MigrationBuilder.Sql 方法.不幸的是，无法访问 db provider 服务，因此下一个适用于 SQL Server(尽管我尝试仅使用标准 SQL 命令).

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).

让原来的模型如下:

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

public class History
{
    public int Id { get; set; }
}

从Team添加FK到History后就变成:

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; }
}

自动生成的迁移是:

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);
}

现在，在 CreateIndex 命令之前，我们手动插入以下内容:

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");

这个想法很简单.我们在History表中创建一个临时的可空列TeamId，为Team中的每条记录插入一个具有对应TeamId的新记录code> 表，然后使用 History 表中的 TeamId 列更新 Team 表中的 HistoryId 列作为一个键，最后删除临时列.

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.

此时数据转换完成，可以创建FK约束.

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

~~远非良好做法，但可以用作解决方法.~~

Gert Arnold 的评论，看起来使用 SQL 块是正确的方法.我唯一关心的是如何编写与数据库无关的和/或特定的 SQL.当然，如果针对单个特定数据库类型，则问题不存在.无论如何，如果需要处理不同的数据库类型，我们总是可以使用所有目标数据库支持的标准 SQL 命令，并结合基于 MigrationBuilder.ActiveProvider 属性.

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.

在 EF Core 中添加一对一关系时迁移数据?

相关推荐