使用实体框架中的更新的DatabaseGeneratedOption迁移实体 [英] Migrate entity with updated DatabaseGeneratedOption in Entity-Framework

问题描述

我已根据本文创建了代码优先应用-代码优先新数据库。现在，我将更改 Blog.BlogId 的DatabaseGeneratedOption。我以以下方式更改了代码：

 公共类博客
 {
 [Key] 
 [DatabaseGenerated（DatabaseGeneratedOption.None）] 
 public int BlogId 
 {
 get; set; 
} 
 ... 
} 
  

并为这段代码更新：

 公共重写void Up（）
 {
 DropForeignKey（ dbo.Posts ， BlogId， dbo.Blogs）； 
 DropPrimaryKey（ dbo.Blogs）; 
 AlterColumn（ dbo.Blogs， BlogId，c => c.Int（nullable：false，identity：false））; 
 AddPrimaryKey（ dbo.Blogs， BlogId）; 
 AddForeignKey（ dbo.Posts， BlogId， dbo.Blogs， BlogId，CascadeDelete：true）; 
} 
  

据此，我更改了在Main函数中创建博客实体的代码（添加了 BlogId 。）

  var Blog = new Blog 
 { 
名称=名称，
 BlogId = 110 //它可以是列
}中未表示的任何其他值； 
  

现在，当我尝试运行代码时，我遇到下一个异常： DbUpdateException 和下一条消息-当IDENTITY_INSERT设置为OFF时，无法在表博客中为标识列插入显式值。

从另一手当我删除所有迁移并从更新的实体创建初始迁移并创建不带标识标志的数据库（不要尝试更新存在的数据库）时，我的代码会使用我的 BlogId 创建实体

我的问题是，在实际项目中我已经创建了表，并且我不会重新创建整个表，只是不会更新键列。

解决方案

您要尝试从列中删除IDENTITY属性，不幸的是，通常这并非易事（至少对于SQL Server，我认为这是不可能的。）。

有关某些说明，请参见：

• 使用Entity Framework手动输入密钥

  
  

  EF将其放入迁移过程中：

    AlterColumn（ dbo.Events， EventID，c => c.Int（nullable：false，identity：false））
    

  生成的sql是这样的：

    ALTER TABLE [dbo]。[事件] ALTER COLUMN [EventID] [int]非空`
    

  实际上确实是深蹲。

  
  




• https://dba.stackexchange.com/questions/87420/how-to-remove-identity-specification-for-multiple-tables



• 从表中的列中删除身份


• > http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a -custom-migration-operation /

---

最后几个链接提供一些有关如何自定义迁移以删除IDENTITY列并仍保留数据的想法。例如，从最后一个链接：

更改SQL Server列上的标识设置的步骤为：

1. 删除所有指向我们正在更改的主键的外键约束


2. 删除主键约束


3. 重命名现有列（以便我们以后可以重新创建外键关系）


4. 使用新的标识设置添加新的主键列


5. 更新现有数据，以便保留以前的外键关系


6. 如果新列是一个标识列，我们需要使用新列更新所有外键列值


7. 如果新列上没有标识，我们可以复制上一个标识列中的旧值


8. 删除旧主键列


9. 添加主键约束


10. 重新添加外键约束

http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/

I have created code-first app according to this article - Code First to a New Database. Now I am going to change DatabaseGeneratedOption for Blog.BlogId. I changed my code in next way:

public class Blog
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int BlogId
    {
        get;set;
    }
    ...
}

And created migration for this code update:

public override void Up()
{
    DropForeignKey("dbo.Posts", "BlogId", "dbo.Blogs");
    DropPrimaryKey("dbo.Blogs");
    AlterColumn("dbo.Blogs", "BlogId", c => c.Int(nullable: false, identity: false));
    AddPrimaryKey("dbo.Blogs", "BlogId");
    AddForeignKey("dbo.Posts", "BlogId", "dbo.Blogs", "BlogId", cascadeDelete: true);
}

According to this I changed code that created blog entity in Main function (added BlogId there.)

var blog = new Blog
{
    Name = name,
    BlogId = 110//it could be any other value that isn't represented in column
};

Now when I am trying to run my code I am getting next exception: DbUpdateException with next message - Cannot insert explicit value for identity column in table 'Blogs' when IDENTITY_INSERT is set to OFF.

From other hand when I delete all migrations and create initial migration from updated entity and creating db without identity flag (don't trying to update exist db), my code that create entity with my BlogId works.

My issue that in real project I have created table and I don't wont to recreate entire table just wont to update key column. How to do it with entity framework migration?

解决方案

You're trying to drop an IDENTITY property from a column, and unfortunately, that's usually not trivial (and for SQL Server at least, I don't think it's possible).

For some explanation, see:

• Entering keys manually with Entity Framework

  EF puts this in the migration:

  AlterColumn("dbo.Events", "EventID", c => c.Int(nullable: false, identity: false))
  

  And the sql generated is this:

  ALTER TABLE [dbo].[Events] ALTER COLUMN [EventID] [int] NOT NULL`
  

  Which actually does diddly squat.

• https://dba.stackexchange.com/questions/87420/how-to-remove-identity-specification-for-multiple-tables

• Remove Identity from a column in a table
• http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/

---

The last few links also provide some ideas for how you can customize your migration to remove the IDENTITY column and still keep your data. For example, from the last link:

The steps for changing the identity setting on a column in SQL Server are:

1. Drop all foreign key constraints that point to the primary key we are changing
2. Drop the primary key constraint
3. Rename the existing column (so that we can re-create the foreign key relationships later)
4. Add the new primary key column with the new identity setting
5. Update existing data so that previous foreign key relationships remain
6. If the new column is an identity column, we need to update all foreign key columns with the new values
7. If the new column doesn’t have identity on, we can copy the old values from the previous identity column
8. Drop old primary key column
9. Add primary key constraint
10. Add back foreign key constraints

http://romiller.com/2013/04/30/ef6-switching-identity-onoff-with-a-custom-migration-operation/

这篇关于使用实体框架中的更新的DatabaseGeneratedOption迁移实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！