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

Probably you need to set the model that `id` is autoincrement identity field by using `[Key]` and `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]` attributes. — Tetsuya Yamamoto, Mar 30 '17 at 08:29
Thanks for the reply but i am new to sql server and entity framework and i didn't understand where to set these attributs. — RoyNasr, Mar 31 '17 at 06:07
Do I have to create a class for each table in my db and my these attributes in the beginning of the class User? I didn't create a class for each table that's why I'm asking. Thanks for your help — RoyNasr, Mar 31 '17 at 06:49
Have you using Code First (generate DB based from code) or DB First (generate model classes from DB)? I want to clarify it first before giving answer, since they have different ways to set identity column. — Tetsuya Yamamoto, Mar 31 '17 at 06:55

bruno.almeida · Answer 1 · 2017-03-30T08:47:35.600

0

Try this:

SET IDENTITY_INSERT sometableWithIdentity ON

edited Mar 30 '17 at 08:47

answered Mar 30 '17 at 08:30

bruno.almeida

2,746
1
24
32

This is not a good practice while using EF, since it has side effect which allowing duplicate values in identity column (read http://stackoverflow.com/questions/21441792/sql-cannot-insert-explicit-value-for-identity-column-in-table-table-when-ident for details). – Tetsuya Yamamoto Mar 30 '17 at 08:34
@Tetsuya Yamamoto, i removed the link, because it is related to other question. RoyNasr said "I have set the identity column in the db to "id" and it is set to auto-increment by 1 starting from 1", so i think the identity is off on the table. – bruno.almeida Mar 30 '17 at 08:49

score 0 · Accepted Answer · edited May 23 '17 at 12:17

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?

Thank you so much I put "StoreGeneratedPattern" to "Identity" and it finally worked!!! Thanks a lot for your help. — RoyNasr, Mar 31 '17 at 10:25

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

2 Answers2

Linked