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

1
votes

I have simple DataTable

CREATE TABLE [dbo].[Table] (
[Id]   INT        IDENTITY (1, 1) NOT NULL,
[Name] NCHAR (10) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

With one record, Name="Test" Id get's automatic 0.

When I am using basic create operation 

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(Table table)
{
    if (ModelState.IsValid)
    {
        db.Table.Add(table);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(table);
}

I get error:

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

What I am doing wrong? How to fix this error?

Table class

public partial class Table
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
}
3
c#asp.netsql-serverasp.net-mvcentity-framework-5
If you are using data base first approach why are you generating models manually and not using entity data model to generate it automatically(as it is done here: asp.net/mvc/overview/getting-started/database-first-development/…) - Alex Art.

3 Answers

1
votes

You need to set IsDbGenerated to true. Add System.Data.Linq to your references then:

[System.Data.Linq.Mapping.Column(Storage = "Id", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }
0
votes

It smells like the table that you are retrieving from the argument (Table table) is setting the property Id to other value than 0.

If that's the case, be sure that you set the Id to 0 before db.Table.Add(table);

If you want to update the item if it exists, then you need to check if the Id is 0 before adding it.

if(table.Id == 0)
   db.Table.Add(table);
db.SaveChanges();
0
votes
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

This worked for me.