SqlException:impossibile inserire un valore esplicito per la colonna Identity

SqlException:impossibile inserire un valore esplicito per la colonna Identity

Quando hai una tabella con una colonna identità e provi a specificare il valore per la colonna identità quando inserisci un record, otterrai la seguente eccezione:

Questo errore significa che hai una colonna di identità nella tabella e stai cercando di impostare un valore per essa. Quando hai una colonna di identità come questa, il suo valore viene generato automaticamente quando la inserisci, ecco perché ti viene impedito di passare un valore per questa colonna.

Ad esempio, supponiamo che la tua tabella abbia la seguente definizione:

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)

Mostrerò alcune soluzioni diverse per risolvere questo problema.

Nota:le soluzioni seguenti mostrano esempi di codice che utilizzano EF Core. Se stai usando ADO.NET o un ORM diverso (come Dapper), anche le stesse soluzioni funzionerebbero (solo con codice diverso).

Opzione 1:non specificare la colonna dell'identità durante l'inserimento

La prima opzione è la più semplice:non provare a impostare il valore per la colonna identità:

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)

Quando inserisci il record, SQL Server genererà il valore per te ed EF Core aggiornerà la proprietà con il valore generato automaticamente.

Opzione 2:attiva IDENTITY_INSERT

In alcuni casi, potresti voler impostare esplicitamente l'id invece di lasciarlo generare automaticamente per te. In questo caso, dovresti attivare IDENTITY_INSERT, in questo modo:

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)

Nota:se utilizzi EF Core, devi eseguire la query all'interno di una transazione affinché funzioni.

IDENTITY_INSERT può essere attivo solo per una tabella alla volta per sessione.

Supponiamo che tu provi ad attivare IDENTITY_INSERT per due tabelle contemporaneamente:

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

Avrai la seguente eccezione:

Questa restrizione si applica solo per sessione. Se qualche altra sessione attiva IDENTITY_INSERT per la tabella degli attori nella loro sessione, puoi attivare IDENTITY_INSERT per i film contemporaneamente in una sessione diversa.

Opzione 3:rimuovere la specifica IDENTITY dalla colonna

Se ti trovi in un ambiente di sviluppo e non ti sei reso conto di avere una colonna Identity finché non ti sei imbattuto in questa eccezione di inserimento identità, è probabile che tu voglia semplicemente rimuovere la specifica IDENTITY dalla colonna.

Se utilizzi EF Core per creare le tabelle, usa l'attributo DatabaseGenerated(DatabaseGeneratedOption.None)) per specificare che la colonna non deve essere una colonna Identity.

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 non gestisce correttamente questa modifica dello schema. Invece di provare a farlo come una modifica dello schema, ripeti la migrazione che ha creato la tabella.

Ad esempio, supponiamo che tu abbia due migrazioni – Database_v1_Init e Database_v2_CreateMoviesTable – e desideri modificare la tabella Movies in modo che non abbia la colonna Identity. Per ripetere la migrazione di Database_v2_CreateMoviesTable, attenersi alla seguente procedura:

Migrazione a Database_v1_Init:

dotnet ef database update Database_v1_Init
Code language: PowerShell (powershell)

Rimuovi l'ultima migrazione, che in questo caso è Database_v2_CreateMoviesTable:

dotnet ef migrations remove
Code language: PowerShell (powershell)

Nota:non eliminare semplicemente il file di migrazione, poiché il file di snapshot del modello non sarà più sincronizzato.

Aggiungi l'attributo [DatabaseGenerated(DatabaseGeneratedOption.None)] alla proprietà Movie.Id.

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

Ricrea la migrazione Database_v2_CreateMoviesTable:

dotnet ef migrations add Database_v2_CreateMoviesTable
Code language: PowerShell (powershell)

Guarda il codice sorgente della migrazione generato in _Database_v2_CreateMoviesTable.cs. Innanzitutto, puoi vedere che non sta creando la colonna con una specifica IDENTITY. In secondo luogo, l'unica cosa che questa migrazione dovrebbe fare è creare la tabella Film. Se sta facendo qualcos'altro, è probabile che il file di snapshot del modello sia entrato in uno stato non valido (probabilmente a causa dell'eliminazione manuale dei file di migrazione).

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)

Applica la migrazione:

dotnet ef database update Database_v2_CreateMoviesTable
Code language: PowerShell (powershell)

Ora che quella colonna id non ha più la specifica IDENTITY, puoi inserire i record nella tabella specificando un valore per l'id.

Disclaimer:questo non si applica agli ambienti di produzione. Perderai dati se elimini e ricrei la tabella. Consiglierei questo approccio solo se ti trovi in un ambiente di sviluppo e sei d'accordo con la perdita di dati.

Opzione 4:se stai effettuando un aggiornamento, recupera prima il record

Per eseguire un aggiornamento anziché un inserimento, devi prima recuperare il record. In caso contrario, quando chiami SaveChanges(), EF Core genererà un'istruzione di inserimento. Se hai provato a specificare il valore per la colonna dell'identità, ti imbatterai nell'eccezione di inserimento dell'identità.

Ecco come aggiornare un record recuperandolo prima:

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)