Mastering Dapper Relationship Mappings
Tags: dapper, dotnet, sql, orm
Published: 2023-10-14 10:44:54

---
title: Mastering Dapper Relationship Mappings
published: true
date: 2023-08-12 00:00:00 UTC
tags: dapper,dotnet,SQL,orm
canonical_url: https://www.milanjovanovic.tech/blog/mastering-dapper-relationship-mappings
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zctfb22ke4xnaw2k1ju2.png
---
**Dapper** is a lightweight **object-relational mapper** in .NET. It's popular because it's easy to use and fast at the same time.

Dapper extends the `IDbConnection` interface with methods for sending SQL queries to the database.

But, because of the nature of SQL, mapping the result into an object model can be tricky.

So in this week's newsletter, I'll teach you how to map:

- Simple queries
- One-to-one relationships
- One-to-many relationships
- Many-to-many relationships

Let's dive in.

## Simple Mapping

Let's first see how to do a **simple mapping** using Dapper.

Writing a query with Dapper has three parts:

- Creating an `IDbConnection` instance
- Writing the SQL query
- Calling a method that Dapper exposes

We will write a SQL query to load a set of `LineItem` objects for a specific `Order`.

```csharp
public class LineItem
{
    public long LineItemId { get; init; }

    public long OrderId { get; init; }

    public decimal Price { get; init; }

    public string Currency { get; init; }

    public decimal Quantity { get; init; }
}
```

Here's the SQL query returning the result we need:

```sql
SELECT Id AS LineItemId, OrderId, Price, Currency, Quantity
FROM LineItems
WHERE OrderId = @OrderId
```

I'm parameterizing the `Order` identifier using the `@OrderId` syntax. This is a Dapper convention. It's important that you use **parameterized queries** to **avoid SQL injection attacks**.

The mapping is straightforward in this case because we are only returning one type from the database.

We call the `QueryAsync` method and specify `LineItem` as the return type. Make sure to pass in the arguments for this method, the SQL query, and the `OrderId` parameter. I prefer creating anonymous objects for Dapper parameters.

```csharp
using var connection = new SqlConnection();

var lineItems = await connection.QueryAsync<LineItem>(
    sql,
    new { OrderId = orderId });
```

That's everything you need for a simple mapping.

## Dapper One To One Relationship Mapping

What if the object we want to return from the SQL query contains a **nested object**?

Here's an updated `LineItem` type that also contains a `Product` inside.

```csharp
public class LineItem
{
    public long LineItemId { get; init; }

    public long OrderId { get; init; }

    public decimal Price { get; init; }

    public string Currency { get; init; }

    public decimal Quantity { get; init; }
}

public class Product
{
    public long ProductId { get; init; }

    public string Name { get; init; }
}
```

Now you need to return two types in the same query.

Here's the updated SQL query with a join on the `Products` table:

```sql
SELECT li.Id AS LineItemId, li.OrderId, li.Price, li.Currency, li.Quantity,
       p.Id AS ProductId, p.Name
FROM LineItems li
JOIN Products p ON p.Id = li.ProductId
WHERE li.OrderId = @OrderId
```

This query is more complicated because we need to use Dapper's **multi-mapping** feature.

In the `QueryAsync` method, we specify both `LineItem` and `Product` as return types and `LineItem` as the final return type for the method.

We must also tell Dapper how to map the `LineItem` and `Product` from the result set into a single `LineItem` object.

And we need to specify the `splitOn` argument, which tells Dapper where one object ends and the next begins.

```csharp
using var connection = new SqlConnection();

var lineItems = await connection.QueryAsync<LineItem, Product, LineItem>(
    sql,
    (lineItem, product) =>
    {
        lineItem.Product = product;

        return lineItem;
    },
    new { OrderId = orderId },
    splitOn: "ProductId");
```

We write more code to make this work, but it should be easy to wrap your head around it.

## Dapper One To Many Relationship Mapping

Another frequent situation is mapping a **one-to-many relationship** from SQL into an object model.

Because you are joining two tables, the result set will contain duplicate data on the "one" side of the relationship.

For this example, let's use an `Order` with a list of `LineItem` objects.

```csharp
public class Order
{
    public long OrderId { get; init; }

    public List<LineItem> LineItems { get; init; } = new();
}

public class LineItem
{
    public long LineItemId { get; init; }

    public long OrderId { get; init; }

    public decimal Price { get; init; }

    public string Currency { get; init; }

    public decimal Quantity { get; init; }
}
```

Here's the SQL query returning the data we need from the database:

```sql
SELECT o.Id AS OrderId,
       li.Id AS LineItemId, li.OrderId, li.Price, li.Currency, li.Quantity
FROM Orders o
JOIN LineItems li ON li.OrderId = o.Id
WHERE o.Id = @OrderId
```

We're going to get back duplicate `Order` data because of the `JOIN`. But we only want to return one `Order` with all the line items.

The Dapper mapping function only gives us the `Order` and `LineItem` for the current row in the result set.

One way to solve this is to use a `Dictionary` to store the `Order` and reuse it inside the mapping.

- Store the `Order` in the dictionary if it's not there
- If it is there, add the `LineItem` to the existing `Order` instance

```csharp
using var connection = new SqlConnection();

var ordersDictionary = new Dictionary<long, Order>();

await connection.QueryAsync<Order, LineItem, Order>(
    sql,
    (order, lineItem) =>
    {
        if (ordersDictionary.TryGetValue(order.OrderId, out var existingOrder))
        {
            order = existingOrder;
        }
        else
        {
            ordersDictionary.Add(order.OrderId, order);
        }

        order.LineItems.Add(lineItem);

        return order;
    },
    new { OrderId = orderId },
    splitOn: "LineItemId");

var mappedOrder = ordersDictionary[orderId];
```

A **many-to-many relationship** would use the same idea, except you'll need two dictionaries for each side of the relationship.

## In Summary

**Dapper** is a fantastic library for writing fast database queries using SQL.

Because of how SQL works, mapping into an object model is sometimes complicated.

There are four common scenarios:

- Simple mapping - a flat structure mapped directly from SQL to an object
- One-to-one mapping - provide a mapping function to connect two objects
- One-to-many mapping - manage a dictionary for the "one" side of the relationship
- Many-to-many mapping - same as above, except you need a dictionary for both sides of the relationship

Now you have a cheat sheet for mapping relationships with Dapper.

Hope this was helpful.

I'll see you next week!

* * *

**P.S. Whenever you're ready, there are 2 ways I can help you:**

1. [**Pragmatic Clean Architecture:**](https://www.milanjovanovic.tech/pragmatic-clean-architecture?utm_source=dev.to&utm_medium=website&utm_campaign=cross-posting) This comprehensive course will teach you the system I use to ship production-ready applications using Clean Architecture. Learn how to apply the best practices of modern software architecture. [**Join 1,000+ students here.**](https://www.milanjovanovic.tech/pragmatic-clean-architecture?utm_source=dev.to&utm_medium=website&utm_campaign=cross-posting)

2. [**Patreon Community:**](https://www.patreon.com/milanjovanovic) Think like a senior software engineer with **access to the source code** I use in my YouTube videos and **exclusive discounts** for my courses. [**Join 860+ engineers here.**](https://www.patreon.com/milanjovanovic)