Dapper insert into table that has a composite PK

Question

I have a table that has a primary key composed of two columns, neither of which are auto-incrementing, and my Dapper insert (part of Dapper Extensions) is failing on the insert saying that the first of the two columns does not allow a null, even tho the value I'm passing in is not null.

Table Student:

StudentId (PK, not null)   \_ (combined to form primary key)
StudentName (PK, not null) /
Active                     -- just another column

C#:

public class Student {
  public int StudentId { get; set; }
  public string StudentName { get; set; }
  public bool Active { get; set; }
}

var newStudent = new Student { StudentId = 5, StudentName = "Joe", Active = true };
var insertSuccess = myConn.Insert<Student>(newStudent);

Error:

Cannot insert the value NULL into column 'StudentId', table 'dbo.Student'; column does not allow nulls. INSERT fails.

Dapper is for some reason not getting the StudentId with a value of 5. Do I have to do something special for tables that have combined PK's, or with tables that have PK's that are not auto-incrementing? Thanks.

Does Dapper has an Insert extension method? [Performing Inserts and Updates with Dapper](http://stackoverflow.com/questions/5957774/performing-inserts-and-updates-with-dapper). Does the official `myConn.Execute("INSERT INTO Student (StudentId, StudentName, Active) VALUES (@StudentId, @StudentName, @Active)", new { StudentId = 5, StudentName = "Joe", Active = true});` works? — thepirat000, Mar 17 '14 at 19:18
I'm using Dapper Extensions to do the insert, and that's what's throwing the error. I just tested your suggestion, and that does work. Was hoping to just use the extensions method because it's a lot less text to type out. — Ian Davis, Mar 17 '14 at 19:34
Can you post the _Insert_ extension method code? Is this one: https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/DapperImplementor.cs? — thepirat000, Mar 17 '14 at 19:49

score 11 · Answer 1 · answered Apr 10 '14 at 20:05

11

Adding an AutoClassMapper will change the behavior for all classes. If you wish to handle just this one class you can create a Map for just this class.

public class StudentClassMapper : ClassMapper<Student>
{
    public StudentClassMapper()
    {
        Map(x => x.StudentId).Key(KeyType.Assigned);
        Map(x => x.StudentName).Key(KeyType.Assigned);
        AutoMap();  // <-- Maps the unmapped columns
    }
}

answered Apr 10 '14 at 20:05

Thad

1,518
2
21
37

2

Added this to the docs https://github.com/tmsmith/Dapper-Extensions/wiki/Customized-mapping-for-a-class – Chris Marisic Sep 17 '15 at 22:33

score 6 · Answer 2 · answered May 01 '20 at 21:29

6

Dapper.Contrib offers an annotation to solve this problem.

public class Student {
  [ExplicitKey]
  public int StudentId { get; set; }
  [ExplicitKey]
  public string StudentName { get; set; }
  public bool Active { get; set; }
}

ExplicitKey means it is a key field whose value you must specify; it is not auto-generated by the database.

I'm assuming when you said "Dapper Extensions," you were referring to a different extension library. You may find you can easily switch over to Dapper.Contrib.

answered May 01 '20 at 21:29

Charles Jenkins

340
4
10

3

Although this works I must point out that `Get`, `GetAsync` `GetAll` and `GetAll` does not support composite keys. – Daniel Leach Jun 07 '20 at 08:12
Use one property as ExplicitKey. Then it will work – Yerkon Mar 05 '22 at 09:19

thepirat000 · Answer 3 · 2014-03-17T20:02:41.343

I'm not sure this is the problem, but AFAIK Dapper Extensions doesn't support composite primary keys by default.

You will probably have to code your own AutoClassMapper: https://github.com/tmsmith/Dapper-Extensions/wiki/AutoClassMapper

The default AutoClassMapper makes certain assumptions about your database schema and POCOs:

AutoClassMapper assumes that your table names are singular (Ex: Car table name and Car POCO name).
Each POCO has at least one property named Id or ends with Id.
If multiple properties end with Id, Dapper Extensions will use the first Id property as the primary key.
If the Id property is determined to be an Integer, the KeyType will be set to Identity.
If the Id property is determined to be a Guid, the KeyType will be set to Guid.
If the id property is anything other than an Integer our Guid, the KeyType will be set to Assigned.

Dapper insert into table that has a composite PK

3 Answers3