实体框架核心，代码优先迁移和数据迁移 [英] 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屋！