Using Dapper Multi-Mapping with two Levels


When reading relational data with Dapper, most examples focus on one other entity, which may come in a 1:1 or 1:n relationship. In this blog post I will show how to use Dappers multi-mapping with two 1:n nested relations.

Sample Data Entities

To illustrate the issue, three entities are used. Courses have a number of participants, which have a number of email addresses.


1Linear Algebra 1
2Linear Algebra 2





SQL Query

The SQL query uses left joins to get all courses, related participants and email addresses. Thus empty entities may appear if no participant is associated with a course or no email address with a participant.

SELECT c.Id, c.Title, p.Id, p.FirstName, p.LastName, ea.Id, ea.Email FROM Course c 
LEFT JOIN Participant p ON p.CourseId = c.Id 
LEFT JOIN EmailAddress ea ON ea.ParticipantId = p.Id

Object-Relational Mapping with Dapper


// Course entity
internal class Course
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public IList<Participant> Participants { get; set; } = new List<Participant>();

// Participant entity
internal class Participant
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public IList<EmailAddress> EmailAddresses { get; set; } = new List<EmailAddress>();

// EmailAddress entity
internal class EmailAddress
    public int Id { get; set; }
    public string Email { get; set; } = string.Empty;


The mapping uses two dictionaries to cache existing courses and participants. Since null values can occur (LEFT JOIN), some null-checks are needed. For illustration purposes, we use a SQLite database file, named SampleData.db.

using System.Data.SQLite;
using Dapper;

string connectionString = "Data Source=SampleData.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
    string sql = @"
        SELECT c.Id, c.Title, p.Id, p.FirstName, p.LastName, ea.Id, ea.Email FROM Course c 
        LEFT JOIN Participant p ON p.CourseId = c.Id
        LEFT JOIN EmailAddress ea ON ea.ParticipantId = p.Id";

    // use dictionaries for caching courses and participants
    var courseCache = new Dictionary<int, Course>();
    var participantCache = new Dictionary<int, Participant>();

    // map entities with Dapper, expecting three entities separated by "Id" column
    var courses = connection.Query<Course, Participant, EmailAddress, Course>(
        (course, participant, email) => {
            // try to find existing course entry
            Course courseEntry;
            if (!courseCache.TryGetValue(course.Id, out courseEntry))
                courseCache.Add(course.Id, course);
                courseEntry = course;

            if (participant != null)
                // try to find existing participant entry
                Participant participantEntry;
                if (!participantCache.TryGetValue(participant.Id, out participantEntry))
                    participantCache.Add(participant.Id, participant);
                    participantEntry = participant;

                    // add participant to course, only for non-existing participants

                if (email != null)
                    // add email address to participant if it exists

            return courseEntry;
        }).DistinctBy(course => course.Id); // return distinct courses, using Id field

    // write entities to console
    foreach (var course in courses)
        Console.WriteLine($"#{course.Id} - {course.Title}");
        foreach (var participant in course.Participants)
            Console.WriteLine($"  #{participant.Id} - {participant.FirstName} {participant.LastName}");
            foreach (var email in participant.EmailAddresses)
                Console.WriteLine($"    #{email.Id} - {email.Email}");

Please note the LINQ DistinctBy method to skip duplicate course entries, which was introduced in .NET 6.0. The output should list all courses with related participants and email addresses.

#1 - Linear Algebra 1
  #1 - Joanna Miller
    #1 - joanna@company.com
    #2 - joanna2@private.org
  #2 - Stuart Handco
#2 - Linear Algebra 2
  #3 - John Smith
    #3 - john@sample.net
#3 - Physics

I hope you found this example useful.