Ошибка cannot insert explicit value for identity column in table

There are basically 2 different ways to INSERT records without having an error:

1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY «ID» MUST BE PRESENT

As per the following example from the same Table created with an IDENTITY PRIMARY KEY:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT from the «INSERT INTO» Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error «Cannot insert explicit value for identify column in table…»

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY «ID» MUST BE PRESENT from the «INSERT INTO» Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error «Explicit value must be specified for identity column table…»

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

There are basically 2 different ways to INSERT records without having an error:

1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY «ID» MUST BE PRESENT

As per the following example from the same Table created with an IDENTITY PRIMARY KEY:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT from the «INSERT INTO» Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error «Cannot insert explicit value for identify column in table…»

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY «ID» MUST BE PRESENT from the «INSERT INTO» Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error «Explicit value must be specified for identity column table…»

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

There are basically 2 different ways to INSERT records without having an error:

1) When the IDENTITY_INSERT is set OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT

2) When the IDENTITY_INSERT is set ON. The PRIMARY KEY «ID» MUST BE PRESENT

As per the following example from the same Table created with an IDENTITY PRIMARY KEY:

CREATE TABLE [dbo].[Persons] (    
    ID INT IDENTITY(1,1) PRIMARY KEY,
    LastName VARCHAR(40) NOT NULL,
    FirstName VARCHAR(40)
);

1) In the first example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is OFF. The PRIMARY KEY «ID» MUST NOT BE PRESENT from the «INSERT INTO» Statements and a unique ID value will be added automatically:. If the ID is present from the INSERT in this case, you will get the error «Cannot insert explicit value for identify column in table…»

SET IDENTITY_INSERT [dbo].[Persons] OFF;
INSERT INTO [dbo].[Persons] (FirstName,LastName)
VALUES ('JANE','DOE'); 
INSERT INTO Persons (FirstName,LastName) 
VALUES ('JOE','BROWN');

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE

2) In the Second example, you can insert new records into the table without getting an error when the IDENTITY_INSERT is ON. The PRIMARY KEY «ID» MUST BE PRESENT from the «INSERT INTO» Statements as long as the ID value does not already exist: If the ID is NOT present from the INSERT in this case, you will get the error «Explicit value must be specified for identity column table…»

SET IDENTITY_INSERT [dbo].[Persons] ON;
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (5,'JOHN','WHITE'); 
INSERT INTO [dbo].[Persons] (ID,FirstName,LastName)
VALUES (3,'JACK','BLACK'); 

OUTPUT of TABLE [dbo].[Persons] will be:

ID    LastName   FirstName
1     DOE        Jane
2     BROWN      JOE
3     BLACK      JACK
5     WHITE      JOHN

Working with EF Core 5.0.0-rc2.20475.6, migrated an old EF 6 .NET project.
I used the scaffolding command to generate the models, it worked and a part of the models are like the following example:

public class Betrieb {
    public virtual ICollection<Tagesinkassos> Tagesinkassi { get; set; }
    // many other properties and collections
}

public class Tagesinkassos {
    public long Id { get; set; }
    // other properties
    public virtual TagesinkassosTagesinkasso TagesinkassosTagesinkasso { get; set; }
    public virtual TagesinkassosPostagesinkasso TagesinkassosPostagesinkasso { get; set; }
    public virtual Betrieb Betrieb { get; set; }
}
public class TagesinkassosPostagesinkasso {
    public long Id { get; set; }
    // other properties
    public virtual Tagesinkassos IdNavigation { get; set; }
}
public class TagesinkassosTagesinkasso {
    public long Id { get; set; }
    // other properties
    public virtual Tagesinkassos IdNavigation { get; set; }
}

I moved to EF Core 5 because of the TPT, I edited the models followings

public abstract class Tagesinkassos {
    public long Id { get; set; }
    // other properties
    public virtual Betrieb Betrieb { get; set; }
}
public class TagesinkassosPostagesinkasso : Tagesinkassos {
    // other properties
}
public class TagesinkassosTagesinkasso : Tagesinkassos {
    // other properties
}

Loading data always works.
Update items already present in the Betrieb.Tagesinkassi collection also always works.
If I try to insert a new one, I get the error in the title Cannot insert explicit value for identity column in table 'Tagesinkassos' when IDENTITY_INSERT is set to OFF

I checked the database. The column Tagesinkassos.Id has the Identity Specifcation set to YES. The ids of TagesinkassosPostagesinkasso and TagesinkassosTagesinkasso have the Identity Specifcation set to NO.

Tryied to change from ON to OFF or viceversa, but it tells me that the tables have to de dropped and recreated, I can’t do it.
Is there a way to use the TPT model or do I have to keep the generated one?

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 ADO.NET or a different ORM (like Dapper), then the same solutions 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 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)

• Apply the migration:

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)

Примечание: модератор удалил этот ответ как дубликат и оставил мой другой ответ на вопрос только с тегом sql-server (это был первый вопрос, на который я пришел из Google). Поскольку этот вопрос имеет тег фреймворка сущности, ответ снова публикуется здесь.

Это для EntityFramework Core 3.1.22. Использование неправильного свойства для указания внешнего ключа приводит к тому, что Entity Framework понижает первичный ключ до… чего-то другого. Затем Entity Framework всегда будет пытаться вставить явное значение, которое создает исключение базы данных, поскольку не может вставить значение, которое, как ему было сказано, является первичным ключом и не должно быть вставлено.

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
Inner Exception:
SqlException: Cannot insert explicit value for identity column in table 'FOO' when IDENTITY_INSERT is set to OFF.

Пример кода. У нас есть сопоставление классов 1-к-1:

    public class Foo /* child */
    {
        public int FooPrimaryKey { get; set; }
        public int BarPrimaryKey { get; set; }
        public virtual Bar PropertyBar {get; set; }
    }

    public class Bar
    {
        public int BarPrimaryKey { get; set; }
        public virtual Foo PropertyFoo {get; set; }
    }

    modelBuilder.Entity<Foo>(entity =>
    {
        entity.HasKey(e => e.FooPrimaryKey);

        entity.ToTable("FOO", "dbo");
        
        entity.HasOne(d => d.PropertyBar)
            .WithOne(x => x.PropertyFoo)
            // wrong, this throws the above exception
            .HasForeignKey<Bar>(x => x.BarPrimaryKey);
    });

Вместо этого внешний ключ должен быть (тот же ключ, другой тип):

    .HasForeignKey<Foo>(x => x.BarPrimaryKey);