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

Question

I am trying to insert data using Dapper.Contrib into a table where the primary key column is not an identity column.

The database table is created using 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 such data:

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

I get the following error:

Cannot insert NULL value in column "Id", table "FooDatabase.dbo.Foos"; column does not allow zeros. INSERT does not work.

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 this is not an identifier column?

Answer 1

You can use the ExplicitKey attribute as this problem .

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

Note that the return value is not the identifier of the inserted element, as usual when you call Insert , but instead always 0 .