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

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.