Inserting One to Many Entities using dapper

Question

I have following two classes and corresponding db tables. I am trying to insert the full object graph (student with multiple courses). I am looking for an example on how would one do this using Dapper. The Id's are auto-increment identity fields.

Class

public class Student
{
    public int Id {get;set;}
    public string Name {get;set;}
    public IEnumerable<Course> Courses {get;set;}
}
public class Course
{
    public int Id {get;set;}
    public string Name {get;set;}
}

Table

Student
Id [int] (pk)
Name [varchar(50)]

StudentCourse
StudentId [int] (fk)
CourseId [int] (fk)

Course
Id [int] (fk)
Name [varchar(50)]

Sam Saffron Sam Saffron · Accepted Answer · 2011-07-21T06:27:53

Dapper has no general helper that would solve all of this for you ... however it is quite easy to wire in the trivial case:

Student student;
// populate student ... 

student.Id = (int)cnn.Query<decimal>(@"INSERT Student(Name) values(@Name)
select SCOPE_IDENTITY()", student);

if (student.Courses != null && student.Courses.Count > 0)
{
   foreach(var course in student.Courses)
   {
      course.Id = (int)cnn.Query<decimal>(@"INSERT Course(Name) values(@Name)
select SCOPE_IDENTITY()", course);
   }
   cnn.Execute(@"INSERT StudentCourse(StudentId,CourseId) 
values(@StudentId,@CourseId)", 
   student.Courses.Select(c => new {StudentId = student.Id, CourseId = c.Id}));
}

One interesting note about this sample is that dapper is able to handle IEnumerable as an input param and dispatch multiple commands for each member of the collection. This allows for efficient param reuse.

Of course, stuff gets a bit tricky if portions of the graph already exist in the db.

Inserting One to Many Entities using dapper

Class

Table

1 Answers