SqlException: Cannot insert explicit value for identity column

Let's explore this issue with an example using a table named Employees.

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
      NOT NULL,
      NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [Salary] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This table has an EmployeeId column defined as an identity column, meaning SQL Server will automatically generate its value when you insert a new record.

If you attempt to explicitly specify the value for EmployeeId, you will receive an error similar to:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table ‘Employees’ when IDENTITY_INSERT is set to OFF.

Here are some solutions to resolve this issue:

Do Not Specify the Identity Column When Inserting

The simplest solution is to avoid specifying the value for the identity column (EmployeeId) when adding a new record. SQL Server will automatically generate the value for you.

Example in C# using Entity Framework (EF Core):

using (var context = new CompanyContext(connectionString))
{
    context.Employees.Add(new Employee()
    {
        Name = "John Doe",
        Position = "Software Developer",
        HireDate = DateTime.Now,
        Salary = 95000.00
    });

    context.SaveChanges();
}

Here, EmployeeId is omitted in the insert, and SQL Server automatically generates it.

Enable IDENTITY_INSERT Temporarily

In some cases, you may need to manually set the identity column's value. In this case, you can temporarily enable IDENTITY_INSERT to allow explicit insertion of values into the identity column.

using (var context = new CompanyContext(connectionString))
{
    using (var transaction = context.Database.BeginTransaction())
    {
        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Employees ON;");
        
        context.Employees.Add(new Employee()
        {
            EmployeeId = 100,
            Name = "Jane Smith",
            Position = "HR Manager",
            HireDate = DateTime.Now,
            Salary = 120000.00m
        });

        context.SaveChanges();

        context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Employees OFF;");
        transaction.Commit();
    }
}

Note: IDENTITY_INSERT can only be set to ON for one table per session. Attempting to turn it on for multiple tables within the same session will result in an error.

Remove the IDENTITY Specification from the Column

If you are working in a development environment and want to remove the identity specification (e.g., if you don't need auto-incrementing values for the EmployeeId), you can alter the column definition.

Mark the EmployeeId property with the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute:

public class Employee
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int EmployeeId { get; set; }
    // other properties...
}

Remove the existing migration and recreate it without the identity specification for the EmployeeId:

dotnet ef database update InitialMigration
dotnet ef migrations remove
dotnet ef migrations add RemoveIdentityFromEmployeeId
dotnet ef database update

Fetch the Record Before Updating

If you're performing an update instead of an insert, make sure to fetch the existing record before making any changes. This avoids an attempt to insert a new record with an explicit value for the identity column.

Example of updating an existing record:

using (var context = new CompanyContext(connectionString))
{
    var employee = await context.Employees.FirstOrDefaultAsync(e => e.EmployeeId == 100);
    if (employee != null)
    {
        employee.Position = "Senior Software Developer";
        employee.Salary = 110000.00;

        context.SaveChanges();
    }
}

In this case, EF Core knows it’s an update and won't try to insert a new record, avoiding the identity column conflict.