在EF Core中添加一对一关系时迁移数据？ [英] Migrating data when adding one-to-one relationship in 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

...这里有历史记录

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

我现在的问题是，但是我的团队中已有团队DB，他们需要有唯一的历史记录行指向，因此我需要为每个现有团队创建一个新的历史记录行。

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.

我尝试手动添加条目在我迁移的方法中，但是由于我的模型与存在的模型不匹配g模式，则失败。

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

从队中添加FK 变为历史记录

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 ，插入一条新记录，并带有相应的 团队表中每条记录的TeamId ，然后更新<$ c中的 HistoryId 列c $ c> Team 表，使用 History 表中的 TeamId 列作为键，最后删除临时列。

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.

Far

编辑：在 Gert Arnold的评论，看起来使用SQL块是正确的要走的路。我唯一关心的是如何编写与数据库无关的SQL和/或特定的SQL。当然，如果针对单个特定的数据库类型，则该问题不存在。无论如何，如果需要处理不同的数据库类型，则可以始终使用所有目标数据库支持的标准SQL命令，并结合基于if 块https://docs.microsoft.com/zh-cn/ef/core/api/microsoft.entityframeworkcore.migrations.migrationbuilder#Microsoft_EntityFrameworkCore_Migrations_MigrationBuilder_ActiveProvider rel = nofollow noreferrer> 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中添加一对一关系时迁移数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！