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.

Quita

Does Dapper has an Insert extension method? 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? – Decennial

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. – Quita

Can you post the Insert extension method code? Is this one: github.com/tmsmith/Dapper-Extensions/blob/master/…? – Decennial

Answer 1

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
    }
} 

Fauve

Added this to the docs github.com/tmsmith/Dapper-Extensions/wiki/… – Apodosis

Answer 2

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.

Microminiaturization

Although this works I must point out that Get, GetAsync GetAll and GetAll does not support composite keys. – Eikon

Use one property as ExplicitKey. Then it will work – Dornick

Answer 3

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.

Decennial