How do I insert data when the primary key column is not an identity column?

Question

I'm trying to insert data using Dapper.Contrib, in a table where the primary key column is not an identity column.

The database table is created with this script:

begin transaction

create table
    dbo.Foos
    (
        Id int not null,
        Name nvarchar(max) not null
    )
go

alter table
    dbo.Foos
add constraint
    PK_Foos primary key clustered
    (
        Id
    )

go

commit

This is the C# class:

public class Foo
{
    public int Id { get; set; }

    public string Name { get; set; }
}

When inserting data like this:

connection.Insert(new Foo()
{
    Id = 1,
    Name = "name 1"
});

I get the following error:

Cannot insert the value NULL into column 'Id', table 'FooDatabase.dbo.Foos'; column does not allow nulls. INSERT fails.

Dapper correctly assumes, by convention, that Id is the primary key, but it incorrectly assumes that it is an identity column. How can I indicate that it is not an identity column?

Answer 1

You can use the ExplicitKey attribute as per this issue.

public class Foo
{
    [ExplicitKey]
    public int Id { get; set; }

    public string Name { get; set; }
}

Note that the return value is not the id of the inserted item as it usually is when you call Insert but is instead always 0.