Mapping Lists of Nested Objects with Dapper: Multi-Query Approach and Performance Optimization

Technical Challenges of Mapping Nested Objects in Dapper

When working with the lightweight ORM framework Dapper, handling complex data structures that contain nested object lists presents a common technical challenge. Unlike full-featured ORMs such as Entity Framework, Dapper does not provide automatic object-relational mapping, requiring developers to manually manage the loading and mapping of associated data. In a course management system example, the Course class includes a Locations property of type IList<Location>, indicating that a course can be taught at multiple locations. This one-to-many relationship is typically implemented at the database level through foreign key associations between Course and Location tables.

Core Implementation of Multi-Query Mapping Strategy

Based on best practices, a stepwise query approach is recommended for mapping nested objects. First, execute a base query to retrieve all course data:

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

This step returns a collection of Course objects, but the Locations property remains empty at this stage. Next, it is necessary to obtain the association mapping data between courses and locations. By extracting the set of course IDs, parameterized queries can be used to efficiently retrieve associated records:

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

The key here is using the @Ids parameter and an anonymous object to pass the ID list; Dapper automatically converts this into a safe parameterized query. Then, based on the location IDs in the mapping data, query the specific location information:

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

In-Memory Mapping and Data Assembly

After obtaining all necessary data, object assembly must be completed in memory. First, create a dictionary mapping location IDs to Location objects to improve lookup efficiency:

var locationDict = locations.ToDictionary(l => l.Id);

Then, based on the CourseLocation mapping data, populate the Locations collection for each course object:

foreach (var course in courses)
{
    var courseLocations = mappings
        .Where(m => m.CourseId == course.Id)
        .Select(m => locationDict[m.LocationId]);
    course.Locations = new List<Location>(courseLocations);
}

Although this method requires manual mapping logic, it provides complete control over the data loading process, avoiding unnecessary database queries.

Performance Optimization and Considerations

When using IN clauses for batch queries, it is important to consider database parameter limitations. SQL Server by default supports up to 2100 parameters; queries will fail if the number of IDs exceeds this limit. Solutions include modifying the query logic:

var mappings = cnn.Query<CourseLocation>(
    "select * from CourseLocations where CourseId in (select Id from Courses where Category = 1)");

Or using Dapper's QueryMultiple method to execute multiple queries and retrieve all results in a single database round-trip:

using (var multi = cnn.QueryMultiple(
    "select * from Courses where Category = 1; " +
    "select * from CourseLocations where CourseId in (select Id from Courses where Category = 1); " +
    "select * from Locations where Id in (select LocationId from CourseLocations where CourseId in (select Id from Courses where Category = 1))"))
{
    var courses = multi.Read<Course>().ToList();
    var mappings = multi.Read<CourseLocation>().ToList();
    var locations = multi.Read<Location>().ToList();
    // Subsequent mapping logic
}

This approach reduces database round-trips and is particularly suitable for scenarios with large data volumes.

Comparison with Other Mapping Methods

In addition to the multi-query approach, the community has proposed single-query solutions with lookup dictionaries. This method retrieves all data through a single JOIN query, then uses a dictionary for deduplication in memory:

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);
        return course;
     }).AsQueryable();
var resultList = lookup.Values;

Although the code is more concise, this method may increase data transfer volume, especially when course and location data are extensive, because JOIN queries create Cartesian product effects. A simplified version attempts to omit the lookup dictionary:

var coursesWithLocations = 
    conn.Query<Course, Location, Course>(@"
        SELECT c.*, l.*
        FROM Course c
        INNER JOIN Location l ON c.LocationId = l.Id                    
        ", (course, location) => {
            course.Locations = course.Locations ?? new List<Location>();
            course.Locations.Add(location); 
            return course;
        }).AsQueryable();

However, this approach results in duplicate Course objects, as Dapper creates new object instances for each query row and cannot automatically merge courses with the same ID.

Practical Application Recommendations

When selecting a mapping strategy, specific application scenarios must be considered. For situations with small data volumes or low query frequencies, the single-query approach with lookup dictionaries can offer cleaner code. For performance-sensitive production environments or large data sets, the multi-query strategy is recommended because it: 1) avoids potential performance issues with JOIN queries; 2) provides better query optimization opportunities; 3) more easily implements advanced features like pagination and lazy loading. Regardless of the chosen method, attention should be paid to parameterized queries to prevent SQL injection, and caching should be used appropriately to reduce database load. By understanding Dapper's design philosophy and the flexibility of manual mapping, developers can effectively handle complex object-relational mapping requirements while maintaining high performance.