liveBook · Manning

Identity_insert

As explained in Building Web APIs with ASP.NET Core

IDENTITY_INSERT is a SQL Server session setting that controls whether explicit values may be inserted into an identity column.

IDENTITY_INSERT is a SQL Server session setting that controls whether explicit values can be written to an identity column. It is OFF by default, so the database normally generates and increments the column value. section 5.3.6

When seeding BoardGames data, assigning existing IDs while IDENTITY_INSERT is OFF causes a DbUpdateException. Preserve those IDs by enabling the setting immediately before saving, then disabling it afterward. section 5.3.6

Wrap explicit-ID inserts in a transaction

using var transaction = _context.Database.BeginTransaction(); _context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT BoardGames ON"); await _context.SaveChangesAsync(); _context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT BoardGames OFF"); transaction.Commit(); The transaction keeps enabling, inserting, and disabling atomic. ExecuteSqlRaw is required because IDENTITY_INSERT is specific to SQL Server. section 5.3.6

How Building Web APIs with ASP.NET Core covers it

How deep it goes
Explains the default, failure cause, alternative fixes, transaction, and raw SQL implementation.
Examples
Demonstrates a BoardGames seed operation that toggles IDENTITY_INSERT around SaveChangesAsync.
What it assumes
Assumes familiarity with EF Core entities, identity columns, SaveChangesAsync, raw SQL, and transactions.
What you can do after
Operate/configure SQL Server identity insertion safely during an EF Core seed transaction.
How it connects
Connects identity columns with EF Core, SQL Server, raw SQL, transactions, and database seeding.
Cover of Building Web APIs with ASP.NET Core
liveBookmanning.com
Read Building Web APIs with ASP.NET Core on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of Building Web APIs with ASP.NET Core. The section links point to the parts of the book it draws on.