Using Dapper Multi-Mapping with two Levels

Motivation

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.

Course

Participant

EmailAddress

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

Entities

// 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;
}

Mapping

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>(
        sql, 
        (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
                    courseEntry.Participants.Add(participantEntry);
                }

                if (email != null)
                {
                    // add email address to participant if it exists
                    participantEntry.EmailAddresses.Add(email);
                }
            }

            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.