实体框架核心，代码优先迁移和数据迁移

本文介绍了实体框架核心，代码优先迁移和数据迁移的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我尝试先使用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):

创建新的 Venue 表

VenueName 列

设置两者之间的外键约束

但是，我需要做的是在第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.

另请参见自定义迁移操作。