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