IDENTITY INSERT and LINQ to SQL

Question

I have a SQL Server database. This database has a table called Item. Item has a property called "ID". ID is the primary key on my table. This primary key is an int with an increment value of 1. When I attempt to insert the record, I receive an error that says:

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

I am attempting to insert records using the following code:

  public int AddItem(Item i)
  {
    try
    {
      int id = 0;
      using (DatabaseContext context = new DatabaseContext())
      {
        i.CreatedOn = DateTime.UtcNow;
        context.Items.InsertOnSubmit(i);
        context.SubmitChanges();
        id = i.ID;
      }
      return id;
    }
    catch (Exception e)
    {
      LogException(e);
    }
  }

When I look at i.ID before submitting it, I notice that i.ID is set to 0. Which would imply that I'm trying to insert 0 as the identity. However, I'm not sure what it should be. Can someone help me out?

Thanks!

Answer 1

Check your ID property inside the Item class to ensure that it have attributes like this:

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert,
    DbType="INT NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]

Look at the IsDbGenerated=true, it is the important guy here.

Maybe you created the DatabaseContext using the designer before adjusting the IDENTITY on the Sql Server, so just regenerate this class (by deleting the table in the designer and dropping it from the Server Explorer again).

Answer 2

It sounds simply as though your model is unaware of the fact that it is an identity; the ID column should be marked as db-generated (Auto Generated Value in the UI, or IsDbGenerated in the xml), and probably as the primary key. Then it will not attempt to insert it, and will update the value correctly after it is written.