我正在尝试使用 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 和 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.
另见自定义迁移操作.
更多推荐
实体框架核心,代码先迁移与数据迁移
发布评论