c# - 在实体框架中更改数据库结构和移动数据
问题描述
我的数据库有实体Driver
,DriverWork
如下所示:
编辑:
public class Driver
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
public class DriverWork
{
[Key]
public int Id { get; set; }
public string FromLocation { get; set; }
public string ToLocation { get; set; }
public int Price { get; set; }
public DateTime Date { get; set; }
public int DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public Driver Driver { get; set; }
}
并且在这些实体中包含许多行。现在我添加了另一个带有 name 的实体WorkPage
,它改变了关系:
DriverWork --> Driver
对此:
DriverWork --> WorkPage --> Driver
更改后的型号(Driver
仍然是旧型号):
public class Driver
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
}
public class WorkPage
{
[Key]
public int Id { get; set; }
public byte CommissionPercentage { get; set; }
public bool IsClosed { get; set; }
public DateTime? DateClosed { get; set; }
public int DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public Driver Driver { get; set; }
}
public class DriverWork
{
[Key]
public int Id { get; set; }
public string FromLocation { get; set; }
public string ToLocation { get; set; }
public int Price { get; set; }
public DateTime Date { get; set; }
public int WorkPageId { get; set; }
[ForeignKey(nameof(WorkPageId))]
public WorkPage WorkPage { get; set; }
}
添加新迁移后,我知道我应该对Up
方法进行更改,因为某些驱动程序当前没有任何WorkPage
s 并且我至少应该WorkPage
为它们添加一个,当前DriverWork
项目必须将它们的值更改为与关联DriverId
的有效值WorkPageId
那个Driver
。但我不知道我应该如何在 EF6 中进行此迁移?
在不更改Up
方法的情况下,EF 在运行update-database
命令后会出现此错误:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.DriverWorks_dbo.WorkPages_WorkPageId". The conflict occurred in database "{dblocation}\KHORSHIDDATA.MDF", table "dbo.WorkPages", column 'Id'.
有什么帮助吗?
解决方案
因为这是一个艰难的转变,我建议通过两次传递来执行它。
首先,将DriverId
FK 保留在DriverWork
and makeDriverId
中是WorkPage
可选的(可为空的):
public class WorkPage
{
[Key]
public int Id { get; set; }
public byte CommissionPercentage { get; set; }
public bool IsClosed { get; set; }
public DateTime? DateClosed { get; set; }
public int? DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public Driver Driver { get; set; }
}
public class DriverWork
{
[Key]
public int Id { get; set; }
public string FromLocation { get; set; }
public string ToLocation { get; set; }
public int Price { get; set; }
public DateTime Date { get; set; }
public int DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public Driver Driver { get; set; }
public int WorkPageId { get; set; }
[ForeignKey(nameof(WorkPageId))]
public WorkPage WorkPage { get; set; }
}
生成新的迁移。它应该是这样的:
public override void Up()
{
CreateTable(
"dbo.WorkPage",
c => new
{
Id = c.Int(nullable: false, identity: true),
CommissionPercentage = c.Byte(nullable: false),
IsClosed = c.Boolean(nullable: false),
DateClosed = c.DateTime(),
DriverId = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
.Index(t => t.DriverId);
AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
CreateIndex("dbo.DriverWork", "WorkPageId");
AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");
}
执行迁移。Driver
然后通过删除fromDriverWork
和 make DriverId
in WorkPage
required(非空)来恢复所需的实体模型:
public class WorkPage
{
[Key]
public int Id { get; set; }
public byte CommissionPercentage { get; set; }
public bool IsClosed { get; set; }
public DateTime? DateClosed { get; set; }
public int DriverId { get; set; }
[ForeignKey(nameof(DriverId))]
public Driver Driver { get; set; }
}
public class DriverWork
{
[Key]
public int Id { get; set; }
public string FromLocation { get; set; }
public string ToLocation { get; set; }
public int Price { get; set; }
public DateTime Date { get; set; }
public int WorkPageId { get; set; }
[ForeignKey(nameof(WorkPageId))]
public WorkPage WorkPage { get; set; }
}
生成第二个新迁移。它应该看起来像这样:
public override void Up()
{
DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
DropIndex("dbo.DriverWork", new[] { "DriverId" });
DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
CreateIndex("dbo.DriverWork", "WorkPageId");
AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
DropColumn("dbo.DriverWork", "DriverId");
}
使用Sql
方法填充WorkPage
表数据并WorkTableId
在需要之前更新 FK。例如,在Up
方法的开头插入以下内容:
Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");
执行迁移,您就完成了。
实际上Sql
,转换数据的调用可以在第一个迁移Up
方法的末尾。
事先知道所有这些,您可以保持新模型不变(跳过第一步),只需将生成的迁移Up
方法替换为上述两者的并集,例如
public override void Up()
{
CreateTable(
"dbo.WorkPage",
c => new
{
Id = c.Int(nullable: false, identity: true),
CommissionPercentage = c.Byte(nullable: false),
IsClosed = c.Boolean(nullable: false),
DateClosed = c.DateTime(),
DriverId = c.Int(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
.Index(t => t.DriverId);
AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
CreateIndex("dbo.DriverWork", "WorkPageId");
AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");
Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");
DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
DropIndex("dbo.DriverWork", new[] { "DriverId" });
DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
CreateIndex("dbo.DriverWork", "WorkPageId");
AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
DropColumn("dbo.DriverWork", "DriverId");
}
推荐阅读
- angular - 是否可以在 Angular5 中使用 ViewChild 提交表单?
- android - 重复设置recyclerview项目背景颜色
- python - Python - 与 setup.py 一起安装时,包无法导入子包
- go - 如何向 ZAP 日志条目添加新列/字段?
- java - 使用哪个 JMS 客户端库?
- ms-access - 从 MS Access 外部打开 MS Access 2007 报告
- html - 隐藏数字输入html离子上的旋转按钮
- java - 我怎样才能让倒数计时器等待按钮点击
- android - 启用 proguard 时出现错误
- node.js - 我如何使用 Promise.all 使其工作?