实体框架迁移,取消现有数据的规范化并引入外键
本文关键字:规范化 数据 迁移 框架 取消 实体 | 更新日期: 2023-09-27 18:14:10
我有一个现有的数据库(首先是EF代码(,它在一个表(table1
(中有数据,该表表示枚举(c#(,然后是另一个表的列(table2
(,该表的列包含枚举的一个值。
我需要做的是去规范化table1
,使其具有table2
(枚举值(的外键
就我开始的实体而言:
public class EnvironmentTypes
{
[Key]
public int EnvironmentTypeId { get; set; }
[Required]
[MaxLength(100)]
public string Name { get; set; }
[Required]
[MaxLength(200)]
public string Description { get; set; }
}
对于table1
和
public class EnvironmentDetails
{
[Required]
[Index("IX_AppUserMachine", 1, IsUnique = true)]
[MaxLength(200)]
public string ApplicationName { get; set; }
[Index("IX_AppUserMachine", 2, IsUnique = true)]
[MaxLength(200)]
public string MachineName { get; set; }
[Index("IX_AppUserMachine", 3, IsUnique = true)]
[MaxLength(50)]
public string UserName { get; set; }
[Required]
public EnvironmentType EnvironmentType { get; set; }
}
对于table2
在更改之后,我需要使table2
的实体变成
public class EnvironmentDetails
{
[Required]
[Index("IX_AppUserMachine", 1, IsUnique = true)]
[MaxLength(200)]
public string ApplicationName { get; set; }
[Index("IX_AppUserMachine", 2, IsUnique = true)]
[MaxLength(200)]
public string MachineName { get; set; }
[Index("IX_AppUserMachine", 3, IsUnique = true)]
[MaxLength(50)]
public string UserName { get; set; }
[Required]
[ForeignKey(nameof(EnvironmentTypeId))]
public virtual EnvironmentTypes EnvironmentType { get; set; }
public int EnvironmentTypeId { get; set; }
}
创建迁移提供:
public partial class DenormaliseEnvironmentTypeFromEnvironmentDetails : DbMigration
{
public override void Up()
{
AddColumn("dbo.EnvironmentDetails", "EnvironmentTypeId", c => c.Int(nullable: false);
CreateIndex("dbo.EnvironmentDetails", "EnvironmentTypeId");
AddForeignKey("dbo.EnvironmentDetails", "EnvironmentTypeId", "dbo.EnvironmentTypes", "EnvironmentTypeId", cascadeDelete: true);
DropColumn("dbo.EnvironmentDetails", "EnvironmentType");
}
public override void Down()
{
AddColumn("dbo.EnvironmentDetails", "EnvironmentType", c => c.Int(nullable: false));
DropForeignKey("dbo.EnvironmentDetails", "EnvironmentTypeId", "dbo.EnvironmentTypes");
DropIndex("dbo.EnvironmentDetails", new[] { "EnvironmentTypeId" });
DropColumn("dbo.EnvironmentDetails", "EnvironmentTypeId");
}
}
这对于空数据库来说很好,但如果我在table2
中有数据,那么运行迁移会抛出一个与外键约束有关的错误。
我的想法是,我应该根据之前的数据填充新的EnvironmentTypeId
列,所以我试图修改迁移,在ColumnBuilder
:中添加一个defaultValueSql
参数
AddColumn("dbo.EnvironmentDetails", "EnvironmentTypeId", c => c.Int(nullable: false, defaultValueSql: "(select [EnvironmentTypeId] from [dbo].[EnvironmentTypes] where [dbo].[EnvironmentTypes].[EnumId] = [EnvironmentType])"));
然而,这给出了错误
Error Number:1046,State:1,Class:15
Subqueries are not allowed in this context. Only scalar expressions are allowed.
有没有任何方法可以在不丢失数据(或非法性(的情况下执行此迁移
在@Александр-Пашкин的回答的帮助下,我设法手动修改了迁移以维护现有数据。我最终使用的迁移看起来像:
CreateTable("tmp", c => new
{
DetailsId = c.Int(),
TypeId = c.Int()
});
Sql("INSERT INTO [dbo].[tmp] select d.Id as DetailsId, t.EnvironmentTypeId as TypeId from [dbo].[EnvironmentDetails] d inner join [dbo].[EnvironmentTypes] t on d.EnvironmentType = t.EnumId");
AddColumn("dbo.EnvironmentDetails", "EnvironmentTypeId", c => c.Int(nullable: false, defaultValue: 1));
CreateIndex("dbo.EnvironmentDetails", "EnvironmentTypeId");
AddForeignKey("dbo.EnvironmentDetails", "EnvironmentTypeId", "dbo.EnvironmentTypes", "EnvironmentTypeId", cascadeDelete: true);
DropColumn("dbo.EnvironmentDetails", "EnvironmentType");
Sql("UPDATE [dbo].[EnvironmentDetails] SET [EnvironmentTypeId] = (SELECT t.[TypeId] from [dbo].[tmp] t where t.DetailsId = Id)");
DropTable("tmp");
因此,需要创建一个临时表来保存基于当前数据的表之间的链接
根据需要修改数据模型
则从临时表填充现有的CCD_ 12
终于放下了临时桌子。
您可以使用DbMigration的Sql方法并删除defaultValueSql
Sql("select [EnvironmentTypeId] from [dbo].[EnvironmentTypes]
where [dbo].[EnvironmentTypes].[EnumId] = [EnvironmentType]");