EF Core5.0 中使用备用键(Alternate Keys)替代主键作为被外键关联的目标列
关于备用键的详细内容,请参考官方文档:Alternate Keys - EF Core | Microsoft Docs
假设项目中已经定义了这样一个Entity(映射SysDictionary
表并已经提交到数据库):
/// <summary>
/// 系统字典表
/// </summary>
[Table("SysDictionary")]
public class SysDictionary
{
[Key]
public int Id { get; set; }
[StringLength(50)]
public string Code { get; set; }
[StringLength(1000)]
public string Name { get; set; }
public int? ParentId { get; set; }
public SysDictionary Parent { get; set; }
public ICollection<SysDictionary> Children { get; set; } = new List<SysDictionary>();
}
详细修改步骤如下:
1、更新Entity
原:
...
public int? ParentId { get; set; }
...
修改为:
...
[StringLength(50)]
public string ParentCode { get; set; }
...
2、更新DbContext.OnModelCreating中外键关系
解析:"HasPrincipalKey"后,会在表上增加一条
原:
...
modelBuilder.Entity<SysDictionary>().HasIndex(h => h.Code);
modelBuilder.Entity<SysDictionary>()
.HasOne(c => c.Parent)
.WithMany(p => p.Children)
.HasForeignKey(h => h.ParentId);
...
修改为:
...
modelBuilder.Entity<SysDictionary>().HasIndex(h => h.Code);
modelBuilder.Entity<SysDictionary>()
.HasOne(c => c.Parent)
.WithMany(p => p.Children)
.HasForeignKey(h => h.ParentCode)
.HasPrincipalKey(p => p.Code);//指定Code列为备用键
...
3、add-migration并调整Migration顺序移至外键关系
执行命令add-migration PrincipalKey
(VS 程序包管理控制台)或者dotnet ef migrations add PrincipalKey
(.NET Core CLI)。
如果直接把更新提交到数据库,那么每一行里新生成的ParentCode
列总是为空,会丢失外键关联关系值。因此需要更新Migration 文件的Up
和Down
方法。
然后更新生成的Migration 文件:
Up
方法:找到为列ParentCode
添加外键的操作,在它后面用migrationBuilder.Sql
方法添加执行SQL语句的操作,把原先ParentId
的外键关系移植到ParentCode
。再找到删除ParentId
列的操作,把它移至新添加的执行SQL语句的操作后面。Down
方法:找到为列ParentId
添加外键的操作,在它后面用migrationBuilder.Sql
方法添加执行SQL语句的操作,把原先ParentCode
的外键关系移植到ParentId
。再找到删除ParentCode
列的操作,把它移至新添加的执行SQL语句的操作后面。
总的更新思路是在Up
方法中,先添加新的列和新的外键关系,再通过移植的方法保留原先的外键关系值,最后再删除旧的ParentId
列;在Up
方法中,先通过添加列和外键还原旧的外键关系,再通过移植的方法还原原先的外键关系值,最后再删新的ParentCode
列。调整后的Migration
文件可能是这样的(笔者用的是SQL Server数据库):
public partial class PrincipalKey : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_SysDictionary_SysDictionary_ParentId",
table: "SysDictionary");
migrationBuilder.DropIndex(
name: "IX_SysDictionary_ParentId",
table: "SysDictionary");
migrationBuilder.AlterColumn<string>(
name: "Code",
table: "SysDictionary",
type: "nvarchar(50)",
maxLength: 50,
nullable: false,
defaultValue: "",
oldClrType: typeof(string),
oldType: "nvarchar(50)",
oldMaxLength: 50,
oldNullable: true);
migrationBuilder.AddColumn<string>(
name: "ParentCode",
table: "SysDictionary",
type: "nvarchar(50)",
maxLength: 50,
nullable: true);
migrationBuilder.AddUniqueConstraint(
name: "AK_SysDictionary_Code",
table: "SysDictionary",
column: "Code");
migrationBuilder.CreateIndex(
name: "IX_SysDictionary_ParentCode",
table: "SysDictionary",
column: "ParentCode");
migrationBuilder.AddForeignKey(
name: "FK_SysDictionary_SysDictionary_ParentCode",
table: "SysDictionary",
column: "ParentCode",
principalTable: "SysDictionary",
principalColumn: "Code",
onDelete: ReferentialAction.Restrict);
//移植外键关系
migrationBuilder.Sql(@"
UPDATE A SET A.ParentCode=B.Code
FROM [dbo].[SysDictionary] A
INNER JOIN [dbo].[SysDictionary] B ON A.ParentId=B.Id;");
migrationBuilder.DropColumn(
name: "ParentId",
table: "SysDictionary");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropForeignKey(
name: "FK_SysDictionary_SysDictionary_ParentCode",
table: "SysDictionary");
migrationBuilder.DropUniqueConstraint(
name: "AK_SysDictionary_Code",
table: "SysDictionary");
migrationBuilder.DropIndex(
name: "IX_SysDictionary_ParentCode",
table: "SysDictionary");
migrationBuilder.AlterColumn<string>(
name: "Code",
table: "SysDictionary",
type: "nvarchar(50)",
maxLength: 50,
nullable: true,
oldClrType: typeof(string),
oldType: "nvarchar(50)",
oldMaxLength: 50);
migrationBuilder.AddColumn<int>(
name: "ParentId",
table: "SysDictionary",
type: "int",
nullable: true);
migrationBuilder.CreateIndex(
name: "IX_SysDictionary_ParentId",
table: "SysDictionary",
column: "ParentId");
migrationBuilder.AddForeignKey(
name: "FK_SysDictionary_SysDictionary_ParentId",
table: "SysDictionary",
column: "ParentId",
principalTable: "SysDictionary",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
//移植外键关系
migrationBuilder.Sql(@"
UPDATE A SET A.ParentId=B.Id
FROM [dbo].[SysDictionary] A
INNER JOIN [dbo].[SysDictionary] B ON A.ParentCode=B.Code;");
migrationBuilder.DropColumn(
name: "ParentCode",
table: "SysDictionary");
}
}
a、可能会忽略的一个点是,原先的
Code
列是可空的。但是在使用HasPrincipalKey
方法指定它为备用键后,框架自动把它改为不可空(nullable: false
),并且设置默认值为空字符串(defaultValue: ""
)。
b、在真正的项目中,被设置为备用键的列可能还需要为其设置唯一索引。简洁起见,文中并未这样做。
4、最后,把更新提交到数据库
执行命令update-database
(VS 程序包管理控制台)或者dotnet ef database update
(.NET Core CLI)。
原文:https://blog.csdn.net/iTheoChan/article/details/113186575