System.Data.Entity.Infrastructure.DbUpdateException错误，即使我没有更新id列 [英] System.Data.Entity.Infrastructure.DbUpdateException error even when i am not updating the id column

问题描述

我正在使用一个项目，我正在使用Entity框架来操纵sql server 2014上的DB。
我的代码如下：

I am working on a project where I am using Entity framework to manipulate a DB on sql server 2014. My code is the following:

private void BtnAddUser(object sender, EventArgs e)
    {
        var u = new User();
        u.username = txtBoxNewUser.Text;
        u.password = txtBoxNewPass.Text;
        u.rank = cmbBoxRank.GetItemText(this.cmbBoxRank.SelectedItem);
        using (var db = new ProjetPooEntities2())
        {
            db.Users.Add(u);
            db.SaveChanges();
        }

    }

请注意，代码运行良好但是当我按添加按钮将创建的用户添加到数据库时，db.saveChanges（）上显示错误，它说：

Please note that the code runs perfectly but it when i press the Add button to add the created user to the db an error shows on "db.saveChanges()" and it says:

System.Data.Entity.Infrastructure.DbUpdateException：'更新条目时出错。
内部异常是：
SqlException：当IDENTITY_INSERT设置为OFF时，不能在表'User'中为标识列插入显式值。

System.Data.Entity.Infrastructure.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.' And the inner exception is: SqlException: Cannot insert explicit value for identity column in table 'User' when IDENTITY_INSERT is set to OFF.

我将db中的标识列设置为id，并将其设置为从1开始自动递增1。
我尝试过搜索很多解决方案，但我没有发现。
任何帮助将不胜感激！

I have set the identity column in the db to "id" and it is set to auto-increment by 1 starting from 1. I have tried searching a lot for a solution but i found nothing. Any help will be appreciated!!

推荐答案

假设用户 entity具有以下结构：

Suppose the User entity has this structure:

public class User
{
    public int id { get; set; }
    public string username { get; set; }
    public string password { get; set; }
    public string rank { get; set; }
}

如果使用代码优先，请添加 DatabaseGeneratedAttribute 与选项 DatabaseGeneratedOption.Identity 为此设置 id as身份自动增量属性：

If Code First is used, add DatabaseGeneratedAttribute with option DatabaseGeneratedOption.Identity as this to set id as identity autoincrement property:

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int id { get; set; }
    ... // other properties
}

但是，如果 Database First 被使用，打开EDMX模型设计器，在 id 字段上使用属性上下文菜单，并设置 StoreGeneratedPattern 到身份：

However if Database First is used, open EDMX model designer, use "Properties" context menu on id field and set StoreGeneratedPattern to Identity:

然后，使用XML编辑器打开EDMX文件，确保 StoreGeneratedPattern =Identity在 id 属性如下所示：

Then, open EDMX file with XML editor and ensure it has StoreGeneratedPattern="Identity" on id property as shown below:

<EntityType Name="User">
    <Key>
        <PropertyRef Name="id" />
    </Key>
    <Property Name="id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
</EntityType>

如果以上方法仍然无法工作，您可以执行 SET IDENTITY_INSERT 查询 DbContext 作为（必须使用 StoreGeneratedPattern =无 EDMX或 DatabaseGenerated（DatabaseGeneratedOption.None）在Code First中正常工作）：

If those ways above still won't work thereafter, you can execute SET IDENTITY_INSERT query in DbContext as this (must be reverting with StoreGeneratedPattern="None" in EDMX or DatabaseGenerated(DatabaseGeneratedOption.None) in Code First to work properly):

using (var db = new ProjetPooEntities2())
{
    // taken from /a/31856991
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");
    db.Users.Add(u);
    db.SaveChanges();
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");
}

请注意，这里最底层的方法不是一个好的做法，因为它有可能允许在某些情况下插入重复的值。

Note that this bottom-most approach here considered not a good practice, since it is possible to allow insertion of duplicate values in certain cases.

相关问题：

不要在表'table'中插入标识列的显式值IDENTITY_INSERT设置为OFF

如何强制实体框架插入标识列？

这篇关于System.Data.Entity.Infrastructure.DbUpdateException错误，即使我没有更新id列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！