System.Data.Entity.Infrastructure.DbUpdateException error even when i am not updating the id column

Question

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();
        }

    }

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: '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.

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!!

Answer 1

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; }
}

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
}

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

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>

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.

Related issues:

Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

How can I force entity framework to insert identity columns?

Answer 2

Try this:

SET IDENTITY_INSERT sometableWithIdentity ON