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!

Croissant

are you sure you set the column as the primary key in the DB and that your Linq to Sql generated classes reflect that. I know sometimes I forget to mark a column as a primary key and my generated classes then need to get updated. – Amicable

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.

Dalliance

Answer 2

Set the "Auto Generated" property of the ID Property to "True".

Rudolf

Answer 3

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).

Demakis

This was the key for me. – Diogenes

Answer 4

The ID will be zero until you call context.SubmitChanges();. Step through the code and look at the value after SubmitChanges has been invoked. Remember, the DB is actually assigning the ID (assuming that it's a auto-increment key).

Take a look at this:

Working with Entity Keys

Benedictus

Answer 5

You must define in your mapping that id is generated in the database. The way to do this depends on the type of mapping you are using. if you are using code attributes make sure that you specify IsDbGenerated in the ColumnAttribute for the Id property (or in XML mapping). if you are using dbml file make sure that Auto Generated Value is set to true.

Caracal

Answer 6

The simplest way is: 1. open dbml 2. Select class for inserting 3. Select column with primary-key 4. check Auto Generated Property in properties window (it should be set to True)

Burnedout