Insert value '0' on identity column and let it auto-generate an identity

Question

I'm using an application called AspNetZero (much like AspNet Boilerplate) and I created a migration script in this application.

The migration script looks like this:

migrationBuilder.CreateTable(
    name: "ContractCustomer",
    columns: table => new
    {
        ContractId = table.Column<int>(nullable: false),
        CustomerId = table.Column<int>(nullable: false),
        Id = table.Column<int>(nullable: false).Annotation("SqlServer:Identity", "1, 1"),
        CreationTime = table.Column<DateTime>(nullable: false),
    },
    constraints: table =>
    {
        table.UniqueConstraint("UX", x => new {x.ContractId, x.VerzorgerId});
        table.PrimaryKey("PK_ContractVerzorger", x => x.Id);
    });

So this creates a table with a primarey key on Id which is auto incremented.

But the thing is, with AspNetZero things are a bit automated behind the scenes for you. When I try to insert a Contract with a ContractCustomer I then get the following error:

Cannot insert explicit value for identity column in table 'ContractCustomer' when IDENTITY_INSERT is set to OFF.

When I use SQL Server Profiler I see that it is trying to run the following query:

INSERT INTO [ContractCustomer] ([Id], [ContractId], [CustomerId], [CreationTime])
VALUES (0, 2, 1, '2020-09-12 13:33:54.2629678');

So it is explicity setting the Id to 0. But the part where it saves the changes is happening behind the scenes.

Is there a way to tell SQL Server to ignore the 0 and let it generate its own Id number? Or is there something I can adjust in my migrations script to get this to work?

Answer 1

One way to quickly solve this would be to create an INSTEAD OF trigger. Then just remove the ID and 0 from the actual INSERT that gets executed. Something like this

drop trigger if exists trg_ContractCustomer_instead_of_ins;
go
create trigger trg_ContractCustomer_instead_of_ins on [ContractCustomer]
instead of insert
as
set nocount on;
if exists(select * from inserted)
    INSERT INTO [ContractCustomer] ([ContractId], [CustomerId], [CreationTime])
    select [ContractId], [CustomerId], [CreationTime] from inserted;