SqlException: Cannot insert explicit value for identity column

by

When you have a table with an identity column, and you try to specify the value for identity column when inserting a record, you’ll get the following exception:

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

This error means you have an identity column in the table, and you’re trying to set a value for it. When you have an identity column like this, its value gets automatically generated when you insert it, hence why you are prevented from passing in a value for this column.

For example, let’s say your table has the following definition:

CREATE TABLE [dbo].[Movies](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NOT NULL,
	[YearOfRelease] [int] NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
	[Director] [nvarchar](100) NOT NULL,
	[BoxOfficeRevenue] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)

I’ll show a few different solutions for fixing this problem.

Note: The solutions below are showing code examples using EF Core. If you’re using Dapper to insert records (or using ADO.NET), then the same approaches to solving the problem would work too (just with different code).

Option 1 – Don’t specify the identity column when inserting

The first option is the simplest – don’t try to set the value for the identity column:

using (var context = new StreamingServiceContext(connectionString))
{
	context.Movies.Add(new Movie()
	{
		//Id = 20,
		Name = "Godzilla",
		Description = "Nuclear lizard fights monsters",
		Director = "Gareth Edwards",
		YearOfRelease = 2014,
		BoxOfficeRevenue = 529_000_000.00m
	});

	context.SaveChanges();
}
Code language: C# (cs)

When you insert the record, SQL Server will generate the value for you and EF Core will update the property with the auto-generated value.

Option 2 – Turn on IDENTITY_INSERT

In some cases, you may want to explicitly set the id instead of letting it get auto-generated for you. In this case, you would need to turn on IDENTITY_INSERT, like this:

using (var context = new StreamingServiceContext(connectionString))
{
	using (var transaction = context.Database.BeginTransaction())
	{
		context.Movies.Add(new Movie()
		{
			Id = 20,
			Name = "Godzilla",
			Description = "Nuclear lizard fights monsters",
			Director = "Gareth Edwards",
			YearOfRelease = 2014,
			BoxOfficeRevenue = 529_000_000.00m
		});

		context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Movies ON;");
		context.SaveChanges();
		context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Movies OFF;");
		transaction.Commit();
	}
}
Code language: C# (cs)

Note: If you’re using EF Core, you have to execute the query within a transaction for this to work.

IDENTITY_INSERT can only be ON for one table at a time per session.

Let’s say you try to turn on IDENTITY_INSERT for two tables at once:

context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Movies ON;");
context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.Actors ON;");
Code language: C# (cs)

You’ll get the following exception:

Microsoft.Data.SqlClient.SqlException (0x80131904): IDENTITY_INSERT is already ON for table ‘StreamingService.dbo.Movies’. Cannot perform SET operation for table ‘dbo.Actors’.

This restriction only applies per session. If some other session turns on IDENTITY_INSERT for the Actors table in their session, you can turn on IDENTITY_INSERT for Movies in a different session at the same time.

Option 3 – Remove the IDENTITY specification from the column

If you’re in a dev environment, and didn’t realize you had an identity column until you ran into this identity insert exception, then chances are you simply want to remove the IDENTITY specification from the column.

If you’re using EF Core to create your tables, use the DatabaseGenerated(DatabaseGeneratedOption.None)) attribute to specify that the column shouldn’t be an identity column.

using System.ComponentModel.DataAnnotations.Schema;

public class Movie
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.None)]
	public int Id { get; set; }
	
	//rest of class
}
Code language: C# (cs)

EF Core doesn’t handle this database schema change correctly. Instead of trying to do this as a schema change, redo the migration that created the table.

For example, let’s say you have two migrations – Database_v1_Init and Database_v2_CreateMoviesTable – and you want to change the Movies table so it doesn’t have the identity column. To redo the Database_v2_CreateMoviesTable migration, do the following steps:

  • Migrate down to Database_v1_Init:
dotnet ef database update Database_v1_Init
Code language: PowerShell (powershell)
  • Remove the last migration, which in this case is Database_v2_CreateMoviesTable:
dotnet ef migrations remove
Code language: PowerShell (powershell)

Note: Don’t just delete the migration file, since the Model snapshot file will get out of sync.

  • Add the [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute to the Movie.Id property.
public class Movie
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.None)]
	public int Id { get; set; }
Code language: C# (cs)
  • Recreate the migration Database_v2_CreateMoviesTable:
dotnet ef migrations add Database_v2_CreateMoviesTable
Code language: PowerShell (powershell)
  • Look at the generated migration source code in <timestamp>_Database_v2_CreateMoviesTable.cs. First, you can see that it’s not creating the column with an IDENTITY specification. Second, the only thing this migration should be doing is creating the Movies table. If it’s doing anything else, then it’s likely the Model snapshot file got into an invalid state (probably due to manually deleting the migration files).
public partial class Database_v2_CreateMoviesTable : Migration
{
	protected override void Up(MigrationBuilder migrationBuilder)
	{
		migrationBuilder.CreateTable(
			name: "Movies",
			columns: table => new
			{
				Id = table.Column<int>(type: "int", nullable: false),
				Name = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				YearOfRelease = table.Column<int>(type: "int", nullable: false),
				Description = table.Column<string>(type: "nvarchar(500)", maxLength: 500, nullable: false),
				Director = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false),
				BoxOfficeRevenue = table.Column<decimal>(type: "decimal(18,2)", nullable: false)
			},
			constraints: table =>
			{
				table.PrimaryKey("PK_Movies", x => x.Id);
			});
			
			//rest of class not shown
}
Code language: C# (cs)
dotnet ef database update Database_v2_CreateMoviesTable
Code language: PowerShell (powershell)

Now that that id column no longer has the IDENTITY specification, you can insert records into the table while specifying a value for the id.

Disclaimer: This doesn’t apply to production environments. You’ll lose data if you drop and recreate the table. I would only recommend this approach if you’re in a dev environment and OK with losing data.

Option 4 – If doing an update, fetch the record first

To do an update instead of an insert, have you fetch the record first. Otherwise when you call SaveChanges(), EF Core will generate an insert statement. If you tried to specify the value for the identity column, then you’ll run into the identity insert exception.

Here’s how to update a record by fetching it first:

using (var context = new StreamingServiceContext(connectionString))
{
	var movie = await context.Movies.FirstOrDefaultAsync(t => t.Id == 20);
	movie.Description = "Nuclear lizard fights monsters";
	
	context.SaveChanges();
}
Code language: C# (cs)

Related Articles