“Cannot insert explicit value for identity column

Question

We have recently added a new "level" to our database - added a key "Company_ID" to be above/before the existing ID Identity field in the tables throughout the database.

For example, if a Table had ID then fields, it now has Company_ID, then ID, then the fields. The idea is that this allows ID to auto-increment for each different Company_ID value that is provided to the functionality (Company_ID 1 can have ID 1, 2, 3 etc ; Company_ID 2 can have ID 1, 2, 3, etc).

The auto-increment field remains as ID. An example table is :

    [dbo].[Project](
      [Company_ID] [int] NOT NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DescShort] [varchar](100) NULL,
      [TypeLookUp_ID] [int] NULL,
      [StatusLookUp_ID] [int] NULL,
      [IsActive] [bit] NOT NULL,
    CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
      [Company_ID] ASC,
      [ID] ASC
    )

Before the Company_ID was introduced, to perform a CREATE, we simply populated the DescShort, TypeLookUp_ID, StatusLookUp_ID and IsActive fields, and left ID to be whatever it was by default, possibly 0.

The record was saved successfully, and ID was auto-populated by the database, and then used to perform a SHOW via a View, and so on.

Now, however, we want to set Company_ID to a specified value, leave ID, and populate the fields as before.

    _db.Project.Add(newProject);
    _db.SaveChanges();

Yes, we want to specify the Company_ID value. We want the ID to be auto-populated, as per before. We are getting the error message :

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

Is this caused by specifying the Company_ID, or by the ID field? Do you know how we can rectify this issue?

Answer 1

What worked for me in this instance is to set an attribute on the primary key property in the class:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int DepartmentId { get; set; }

IDENTITY_INSERT was already on in my DB.

Answer 2

Ok, you have a solution already... but consider to leave ID as unique Primary Key and Company_Id as Foreign Key

Answer 3

After sleeping, I found this, for Visual Studio c# code : [DatabaseGenerated(DatabaseGeneratedOption.Identity)]. This (in my words), defined for each of my ID fields, tells Visual Studio that the field is an Identity and to "leave it alone" when sending values to the database. When Company_ID occurs first, and has a value, telling Visual Studio that there is an Identity field elsewhere allows the _db.Project.Add(newProject); and then _db.SaveChanges(); to function as required. This part of the answer is for the Visual Studio side of things. I understand the SQL requirements of IDENTIY_INSERT so thanks to @matt-thrower, @steve-pettifer and the others who contributed.

Answer 4

Can you try 2 things and try each seperately?

• Remove primary key fields for ID and Company_ID
• Take ID column on the first order

Answer 5

The Increment of your Foreign Key in your SQL table is Set automatic ... So when you want to insert any things, you have to delete this Foreign Key from the code like this exemple.

Sql code:

CREATE TABLE [dbo].[annexe1](
[cle] [int] IDENTITY(1,1) NOT NULL,
[tarif] [nvarchar](50) NULL,
[libele] [nvarchar](max) NULL)

Asp.Net code:

InsertCommand = "INSERT INTO [annexe6] ([cle], [tarif], [libele]) VALUES (@cle, @tarif, @libele)"

Correction:

InsertCommand = "INSERT INTO [annexe6] ([tarif], [libele]) VALUES (@tarif, @libele)"

Answer 6

Your ID must be unique. Use some hash for that. (for example GUID)

[Key]
public string Id { get; set; }

public your_constructor()
{
    Id = Guid.NewGuid().ToString();
}