使用 Code First 迁移添加外键 [英] adding a foreign key with Code First migration

问题描述

添加迁移后尝试更新数据库时出现错误.

I have an error when trying to update my database after adding a migration.

这是我在添加迁移之前的课程

Here are my classes before add-migration

public class Product
{
    public Product() { }

    public int ProductId { get; set; } 
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool Istaxable { get; set; }
    public string DefaultImage { get; set; }
    public IList<Feature> Features { get; set; }
    public IList<Descriptor> Descriptors { get; set; }
    public IList<Image> Images { get; set; }
    public IList<Category> Categories { get; set; }
}


public class Feature
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }

}

现在我想在我的 Feature 类中添加一个外键并以这种方式重构这些类:

Now I wanted to add a foreign key in my Feature class and refactored the classes this way:

public class Product
{
    public Product() { }

    public int ProductId { get; set; } 
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool Istaxable { get; set; }
    public string DefaultImage { get; set; }
    public IList<Feature> Features { get; set; }
    public IList<Descriptor> Descriptors { get; set; }
    public IList<Image> Images { get; set; }
    public IList<Category> Categories { get; set; }
}

public class Feature
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string Image { get; set; }
    public string VideoLink { get; set; }

    public int ProductId { get; set; }
    public Product Product { get; set; }
}

我使用 Add-Migration 命令添加了迁移.我添加了一个 Update-Database 命令，这是我得到的结果:

I added a migration with Add-Migration command. I added an Update-Database command and here is what I got back:

ALTER TABLE 语句与 FOREIGN KEY 约束冲突FK_dbo.ProductFeatures_dbo.Products_ProductId".发生了冲突在数据库CBL"中，表dbo.Products"，列ProductId"

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.ProductFeatures_dbo.Products_ProductId". The conflict occurred in database "CBL", table "dbo.Products", column 'ProductId'

我可以做些什么来解决这个问题并让我的迁移恢复正常?

What can I do to solve this problem and get my migrations back to normal?

推荐答案

解决这个问题的关键是将你的迁移分成两个迁移.首先，添加一个可为空的字段并填写数据.其次，将该字段设为必填外键.

The key to solving this problem is to break your migration into two migrations. First, add a nullable field and fill in the data. Second, make the field a required foreign key.

1. 将新属性作为可空类型(例如 int?)添加到您的类中

1. Add the new property to your class as a nullable type (e.g. int?)

public class MyOtherEntity
{
    public int Id { get; set; }
}

public class MyEntity
{
    ...
    // New reference to MyOtherEntity
    public int? MyOtherEntityId { get; set; }
    ...
}

创建迁移.注意:迁移名称并不重要，但像AddBlogPosts1"这样的名称很容易阅读.

Create a migration. NOTE: Migration name is not important, but something like "AddBlogPosts1" makes it easy to read.

> add-migration AddMyEntityMyOtherEntity1

这应该像这样构建一个迁移:

This should scaffold a migration that looks like this:

public partial class AddMyTableNewProperty1 : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());
    }
    public override void Down()
    {
        DropColumn("dbo.MyEntity", "MyOtherEntityId");
    }
}

现在手动编辑生成的迁移，为新字段添加默认值.最简单的情况是默认值不变.如果需要，您可以在 SQL 中添加更多逻辑.此示例假定所有 MyEntity 实例都指向同一个 ID 为 1 的 MyOtherEntity 实例.

Now manually edit the generated migration to add a default value for the new field. The easiest case is when the default value is invariant. You can add more logic in the SQL if needed. This example assumed all the MyEntity instances point to the same MyOtherEntity instance with ID 1.

public partial class AddMyTableNewProperty1 : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());

        // ADD THIS BY HAND
        Sql(@"UPDATE dbo.MyEntity SET MyOtherEntityId = 1
              where MyOtherEntity IS NULL");
    }
    public override void Down()
    {
        DropColumn("dbo.MyEntity", "MyOtherEntityId");
    }
}

更新数据库

Update the database

> update-database

第二次迁移

1. 返回您的 MyEntity 类并更改新属性以表示强制外键.

1. Go back to your MyEntity class and change the new property to represent a mandatory foreign key.

public class MyEntity
{
    ...
    // Change the int? to int to make it mandatory
    public int MyOtherEntityId { get; set; }

    // Create a reference to the other entity
    public virtual MyOtherEntity MyOtherEntity { get; set; }
    ...
}

创建另一个迁移

Create another migration

> add-migration AddMyEntityMyOtherEntity2

这应该会创建如下迁移:

This should create a migration like the following:

public partial class AddMyEntityMyOtherEntity2: DbMigration
{
    public override void Up()
    {
        AlterColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int(nullable: false));
        CreateIndex("dbo.MyEntity", "MyOtherEntityId");
        AddForeignKey("dbo.MyEntity", "MyOtherEntityId", "dbo.MyOtherEntity", "Id");
    }
    public override void Down()
    {
        DropForeignKey("dbo.MyEntity", "MyOtherEntityId", "dbo.MyOtherEntity");
        DropIndex("dbo.MyEntity", new[] { "MyOtherEntityId" });
        AlterColumn("dbo.MyEntity", "MyOtherEntityId", c => c.Int());
    }
}

更新数据库

Update the database

> update-database

其他说明

1. 此技术适用于应用程序启动期间应用的迁移.
2. 可以为 SQL 中的新列添加更复杂的映射，但此处未说明.

这篇关于使用 Code First 迁移添加外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！