实体框架核心，代码先迁移与数据迁移 [英] Entity framework core, code first migration with data migration

问题描述

我正在尝试使用 EF Core 代码优先迁移来对现有表进行反规范化.

I am trying to use EF Core code first migrations to de-normalise an existing table.

我有一个现有表 LoginEvent 其数据如下所示:

I have en existing table LoginEvent which data looks like this:

╔═════════════════════════════════════════════════╗
║                   LoginEvent                    ║
╠════╦══════════╦═══════════╦════════════╦════════╣
║ Id ║ VenueRef ║ VenueName ║ OccurredAt ║ UserId ║
╠════╬══════════╬═══════════╬════════════╬════════╣
║ 1  ║ ven01    ║ Venue 1   ║ 2018-01-29 ║ 5      ║
╠════╬══════════╬═══════════╬════════════╬════════╣
║ 2  ║ ven02    ║ Venue 2   ║ 2018-01-30 ║ 7      ║
╠════╬══════════╬═══════════╬════════════╬════════╣
║ 3  ║ ven01    ║ Venue 1   ║ 2018-02-01 ║ 9      ║
╚════╩══════════╩═══════════╩════════════╩════════╝

public class LoginEvent
{
    [Key]
    public int Id { get; set; }
    public string VenueRef { get; set; }
    public string VenueName { get; set; }
    public DateTime OccurredAt { get; set; }
    public User User { get; set; }
}

我想将其标准化为两个表:LoginEvent 和 Venue，如下所示:

And I want to normalise this into two tables: LoginEvent and Venue, like so:

╔═════════════════════════════════════╗
║             LoginEvent              ║
╠════╦══════════╦════════════╦════════╣
║ Id ║ VenueRef ║ OccurredAt ║ UserId ║
╠════╬══════════╬════════════╬════════╣
║ 1  ║ ven01    ║ 2018-01-29 ║ 5      ║
╠════╬══════════╬════════════╬════════╣
║ 2  ║ ven02    ║ 2018-01-30 ║ 7      ║
╠════╬══════════╬════════════╬════════╣
║ 3  ║ ven01    ║ 2018-02-01 ║ 9      ║
╚════╩══════════╩════════════╩════════╝

╔══════════════════════╗
║        Venue         ║
╠══════════╦═══════════╣
║ VenueRef ║ VenueName ║
╠══════════╬═══════════╣
║ ven01    ║ Venue 1   ║
╠══════════╬═══════════╣
║ ven02    ║ Venue 2   ║
╚══════════╩═══════════╝

现在，我通过添加一个新的 Venue 域对象并让 LoginEvent 引用它来完成此操作，如下所示:

Now, I have done this by adding a new Venue domain object and having the LoginEvent reference it, like so:

public class LoginEvent
{
    [Key]
    public int Id { get; set; }    
    public string VenueRef { get; set; }
    public DateTime OccurredAt { get; set; }
    public Venue Venue { get; set; }
    public User User { get; set; }
}

public class Venue
{
    [Key]
    public string VenueRef { get; set; }
    public string VenueName { get; set; }
}

然后我创建了一个迁移，它(正确地):

I've then created a migration, which (correctly):

1. 创建新的 Venue 表
2. 删除 VenueName 列
3. 设置两者之间的外键约束

但是，我需要能够做的是在步骤 1 和 2 之间运行数据迁移，以便现有 Venues 在删除列和设置约束之前位于新表中(否则，我将丢失数据和约束失败，因为我没有关联的场所).

However, what I need to be able to do is run a data migration between steps 1 and 2 so that the existing Venues are in the new table before dropping the column and before setting up the constraint (otherwise, I'll lose data and the constraint fails as I don't have associated Venues).

我想在我的数据迁移中运行这样的程序:

I'd like to run something like this as my data migration:

INSERT INTO Venue SELECT DISTINCT VenueRef, VenueName FROM LoginEvent

我该怎么做?

推荐答案

您可以使用 migrationBuilder.Sql(theSqlString) 执行任何 SQL.

You can execute any SQL using migrationBuilder.Sql(theSqlString).

就你而言

migrationBuilder.Sql("INSERT INTO Venue SELECT DISTINCT VenueRef, VenueName FROM LoginEvent");

在创建新表 Venue 之后和删除旧列 VenueName 之前在迁移中运行它.

Run this in the migration after creating the new table Venue and before dropping the old column VenueName.

另见自定义迁移操作.

这篇关于实体框架核心，代码先迁移与数据迁移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！