How do I map lists of nested objects with Dapper

Question

I'm currently using Entity Framework for my db access but want to have a look at Dapper. I have classes like this:

public class Course{
   public string Title{get;set;}
   public IList<Location> Locations {get;set;}
   ...
}

public class Location{
   public string Name {get;set;}
   ...
}

So one course can be taught at several locations. Entity Framework does the mapping for me so my Course object is populated with a list of locations. How would I go about this with Dapper, is it even possible or do I have to do it in several query steps?

Answer 1

Dapper is not a full blown ORM it does not handle magic generation of queries and such.

For your particular example the following would probably work:

Grab the courses:

var courses = cnn.Query<Course>("select * from Courses where Category = 1 Order by CreationDate");

Grab the relevant mapping:

var mappings = cnn.Query<CourseLocation>(
   "select * from CourseLocations where CourseId in @Ids", 
    new {Ids = courses.Select(c => c.Id).Distinct()});

Grab the relevant locations

var locations = cnn.Query<Location>(
   "select * from Locations where Id in @Ids",
   new {Ids = mappings.Select(m => m.LocationId).Distinct()}
);

Map it all up

Leaving this to the reader, you create a few maps and iterate through your courses populating with the locations.

Caveat the in trick will work if you have less than 2100 lookups (Sql Server), if you have more you probably want to amend the query to select * from CourseLocations where CourseId in (select Id from Courses ... ) if that is the case you may as well yank all the results in one go using QueryMultiple