How to map object relationships using Dapper in ASP.NET Core

Dapper is a simple, lightweight, and rightfully popular ORM (object relationship mapper) for .NET. We learned the basics of working with Dapper in a previous article. And we discussed working with the Dapper Extensions library in an earlier article. More recently, we explored a few advanced features of Dapper.

In this article, we’ll learn how to work with relationship mappings in Dapper. Specifically, we’ll examine how to map one-to-one, one-to-many, and many-to-many relationships using Dapper.

To use the code examples provided in this article, you should have Visual Studio 2022 installed in your system. If you don’t already have a copy, you can download Visual Studio 2022 here.

Create an ASP.NET Core Web API project in Visual Studio

First off, let’s create an ASP.NET Core 7 project in Visual Studio 2022. Follow these steps:

1. Launch the Visual Studio 2022 IDE.
2. Click on “Create new project.”
3. In the “Create new project” window, select “ASP.NET Core Web API” from the list of templates displayed.
4. Click Next.
5. In the “Configure your new project” window, specify the name and location for the new project.
6. Optionally check the “Place solution and project in the same directory” check box, depending on your preferences.
7. Click Next.
8. In the “Additional Information” window shown next, leave the “Use controllers (uncheck to use minimal APIs)” box checked. We won’t be using minimal APIs in this project. Leave the “Authentication Type” set to “None” (the default).
9. Ensure that the check boxes “Enable Open API Support,” “Configure for HTTPS,” and “Enable Docker” remain unchecked. We won’t be using those features here.
10. Click Create.

We’ll use this ASP.NET Core 7 Web API project to work with advanced features of Dapper in the sections below.

What is Dapper? Why use it?

Object-relational mappers are used to address the “impedance mismatch” between object models in programming languages and data models in relational databases. Dapper is a lightweight, high performance, micro-ORM framework for .NET and .NET Core. It supports a wide range of databases such as Oracle, SQL Server, Oracle, MySQL, PostgreSQL, SQLite, and SQL CE. The Stack Overflow team created Dapper to be a simple ORM for .NET. Dapper is an open-source project available on GitHub.

Multi-mapping in Dapper

Dapper provides support for multi-mapping, which allows you to map a single record from one database table to multiple records from another table. With multi-mapping, you can retrieve data from multiple database tables in one query.

To accomplish this, you use the SplitOn parameter when you call Dapper’s Query method. The SplitOn method specifies which columns to use to split data into multiple objects. Consider the following two classes.

public class Author {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Book {
    public int Id { get; set; }
    public int AuthorId { get; set; }
    public string Title { get; set; }
    public string ISBN { get; set; }
}

The following code snippet illustrates how you can implement multi-mapping using Dapper.

using (var connection = new SqlConnection(connectionString)){
    string query = "SELECT * from Authors A Inner Join Books B ON A.Id = B.AuthorId";
    var authors = connection.Query<Author, Book, Author>(
        query,
        (author, book) =>
        {
            author.Books = author.Books ?? new List<Book>();
            author.Books.Add(book);
        },
    splitOn: "Id"        
    ).Distinct().ToList();
}

Simple mapping in Dapper

Consider a database table named Store with the fields listed in the table below. 

—

The following parameterized SQL query would return a record from the Store database table that has a matching Store_Id:  

Select Store_Id, Store_Name, Location
From Store Where Store_Id = @StoreId 

To retrieve a record from the Store database table using Dapper, use the following code snippet.  

using var connection = new SqlConnection();
var stores = await connection.QueryAsync<Store>(
    sql, new { Store_Id = storeId });

One-to-one relationship multi-mapping in Dapper

Consider two database tables named Order and Customer with the fields listed in the tables below.

—

—

You can use the following SQL query to select an Order record and its associated Customer details.

Select o.Order_Id, o.Order_Date, c.Customer_Id, c.First_Name, c.Last_Name
From Order o
Inner Join Customer c On o.Customer_Id = c.Customer_Id

The following code shows how you can establish a one-to-one mapping between the Order and Customer database tables and retrieve Order records using Dapper.

string query = "Select * From Order o Inner Join Customer c On o.Customer_Id = c.Customer_Id";
var orders = connection.Query<Order, Customer, Order>(query, map:
    (order, customer) =>
    {
      order.Customer = customer;
      return order;
    }, splitOn: "Customer_Id").FirstOrDefault();

One-to-many relationship multi-mapping in Dapper

We’ll now modify the Order table to incorporate an additional field. The following database tables illustrate a one-to-many relationship between two entities, Order and Product, meaning you can have an order that is associated with multiple products.

—

—

You can now use the following SQL query to retrieve all orders.

Select Order_Id, Order_Quantity, Order_Date, p.Product_Name
From Order o
Inner Join Product p On o.Product_Id = p.Product_Id

The following code snippet shows how to establish a one-to-many mapping between Order and Product tables and retrieve Order records using Dapper.

string query = "Select * From Order o Inner Join Product p On o.Product_Id = p.Product_Id";
var orders = connection.Query<Order, Product, Order>(query, map:
    (order, product) =>
    {
      order.Product = product;
      return order;
    }, splitOn: "Product_Id;

Many-to-many relationship multi-mapping in Dapper

The many-to-many relationship is a bit more involved. For example, you can have a relationshop between the Student and Course entities where one student can enroll in multiple courses while you can also have one course mapped to multiple students. In other words, many-to-many relationships allow multiple records in one table to be related to multiple records in another table.

Such relationships are typically implemented using a third table, which is sometimes called a “junction table” or “bridge table.” To map a many-to-many relationship using Dapper, you must handle the join table and make several SQL queries or one well-constructed query to fetch the related data.

Consider the three database tables named Student, Course, and StudentCourse and their respective fields as shown in the tables below.

—

—

—

We can represent the relationship between students and courses using the following classes.

public class Student
    {
        public int Student_Id { get; private set; }
        public string FirstName { get; set; } = string.Empty;
        public string LastName { get; set; }
        public string Address { get; set; } = string.Empty;
        public List<Course> Courses { get; set; }
    }
    public class Course
    {
        public int Course_Id { get; private set; }
        public string Course_Title { get; set; }
        public int Course_Duration { get; set; } 
        public DateTime Start_Date { get; set; }
  public List<Student> Courses { get; set; }
    }

Note the lists in the Student and Course classes. While the Student class contains a list of Courses, the Course class contains a list of Students. You can use the following SQL statement to retrieve a student and its related data.

Select s.Student_Id, s.First_Name, s.Last_Name, c.Course_Title
From Student s
Inner Join Student_Course sc ON sc.Student_Id = s.Student_Id
Inner Join Course c ON c.Course_Id = sc.Course_Id

You can establish a many-to-many mapping between the Student and Course entities in Dapper that allows you to retrieve student records together with the courses they have registered for. Here is the code:

using(var connection = new SqlConnection(connectionString))
{
  string query = "Select s.Student_Id, s.First_Name, s.Last_Name, c.Course_Title From Student s " +
    "Inner Join Student_Course sc ON sc.Student_Id = s.Student_Id " +
    "Inner Join Course c ON c.Course_Id = sc.Course_Id";
  var students = await connection.QueryAsync <Student,
    Course, Student > (query, (student, course) => {
      student.Courses.Add(course);
      return student;
    }, splitOn: "Course_Id");
}

Dapper is a lightweight, performant, and customizable ORM with great features that can significantly improve data access capabilities in your .NET Core applications. As we’ve seen, Dapper provides excellent support for mapping relationships. I’ll have more to say about using Dapper in a future article here.