# Mastering Dapper Relationship Mappings

> Dapper is a lightweight object-relational mapper in .NET, easy to use and fast at the same time. But because of the nature of SQL, mapping the result into an object model can be tricky. Here's how to map simple queries, one-to-one, one-to-many, and many-to-many relationships.

Published: 2023-08-12. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/mastering-dapper-relationship-mappings

Dapper maps a flat result set straight into an object, but relationships need multi-mapping.
For a one-to-one relationship, pass both types to `QueryAsync`, supply a mapping function, and set `splitOn` to the first column of the nested object.
For one-to-many, keep a dictionary so the joined rows reuse the same parent, and many-to-many needs one dictionary per side.

**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 Product Product { 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**](https://milanjovanovic.tech/blog/dapper-dotnet-guide) 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!

---

## Frequently asked questions

### What is Dapper in .NET?

Dapper is a lightweight object-relational mapper for .NET that is popular because it is easy to use and fast at the same time. It extends the IDbConnection interface with methods for sending SQL queries to the database and mapping the results to your objects.

### How do you prevent SQL injection with Dapper?

Use parameterized queries. Dapper uses the @ParameterName convention inside the SQL text, and you pass the values as an argument to the query method, for example as an anonymous object like new { OrderId = orderId }. Never concatenate user input into the SQL string.

### How do you map a one-to-one relationship with Dapper?

Use Dapper's multi-mapping feature. Write a SQL query that uses JOIN on the related table, call QueryAsync with both types and the final return type as generic arguments, and supply a mapping function that assigns the nested object, plus the splitOn argument.

### What does the splitOn parameter do in Dapper?

When a query returns columns for more than one object, splitOn tells Dapper at which column one object ends and the next begins in the result set. You pass the name of the first column of the second object, for example splitOn: "ProductId".

### How do you map a one-to-many relationship with Dapper?

A JOIN duplicates the data on the "one" side of the relationship in every row. Use a dictionary keyed by the parent id inside the mapping function: store the parent the first time you see it, reuse the existing instance afterwards, and add each child row to its collection.

### How do you map a many-to-many relationship with Dapper?

The same way as a one-to-many relationship, with dictionaries that deduplicate repeated rows from the JOIN, except you need two dictionaries, one for each side of the relationship, so both entity types are reused instead of duplicated.