CODEWITHSUNDEEP
c#primary-keydapperdapper-extensions
Ian Davis
Ian Davis

Reputation: 19413

Dapper insert into table that has a composite PK

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.

Upvotes: 13

Views: 17162

Answers (3)

Charles Jenkins
Charles Jenkins

Reputation: 390

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.

Upvotes: 9

Thad
Thad

Reputation: 1530

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

Upvotes: 11

thepirat000
thepirat000

Reputation: 13089

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.

Upvotes: 0

Related Questions