How can I use Dapper to map lists of nested objects?

dapper orm

Question

For my database access, I presently use Entity Framework, but I want to check out 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;}
   ...
}

Therefore, a single course may be taught in many places. Because Entity Framework does the mapping for me, a list of locations is now included in my Course object. Is it even feasible to achieve this with Dapper, or do I need to break it out into many query steps?

1
117
4/23/2018 2:54:45 PM

Accepted Answer

Dapper is not a fully fledged ORM; it doesn't deal with things like magic query creation.

The following likely would be effective for your specific example:

Pick up the courses:

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

Obtain 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 pertinent areas

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

Put everything on a map.

You make a few maps and iterate through your courses, filling them with the places, leaving this up to the reader.

the Caveatin If you have less than 2100 lookups (SQL Server), the method will work; if you have more, you should definitely change the query toselect * from CourseLocations where CourseId in (select Id from Courses ... ) If that's the case, you may as well use the "yank all results at once"QueryMultiple

52
5/23/2017 12:03:05 PM

Popular Answer

As an alternative, you may utilize a lookup with only one query:

var lookup = new Dictionary<int, Course>();
conn.Query<Course, Location, Course>(@"
    SELECT c.*, l.*
    FROM Course c
    INNER JOIN Location l ON c.LocationId = l.Id                    
    ", (c, l) => {
        Course course;
        if (!lookup.TryGetValue(c.Id, out course))
            lookup.Add(c.Id, course = c);
        if (course.Locations == null) 
            course.Locations = new List<Location>();
        course.Locations.Add(l); /* Add locations to course */
        return course;
     }).AsQueryable();
var resultList = lookup.Values;

Zzz-11-Zzz is shown here.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow