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

c#, dapper, dapper-contrib, sql-server

Solution

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`.

Problem

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?

Original source