Managing Relationships With Dapper

Full-featured ORMs like Entity Framework Core are designed to understand and work with relationships. If a related entity is included as part of a query, EF Core will ensure that any retrieved data is automatically mapped to the appropriate association or navigational property.

In SQL, you have a choice in how you go about retrieving related data. You can either perform one query to obtain all the data (a standard JOIN query), or you can perform multiple queries - one to get the parent data, and another to obtain related data. For most scenarios, executing a JOIN query is likely to be the right choice. Each record returned from a JOIN query will include data from multiple objects.

Dapper provides a feature called Multi mapping to enable you to explicitly map data in a single row to multiple objects.

One To Many Relationships

The following class definitions represent a cut-down version of the Product and Category entities from the canonical Northwind sample database:

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    ...
    public Category Category { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    ...
    public ICollection<Product> Products { get; set; }
}

There is an association between the Product and Category classes, represented by the Category property in the Product class, and the Products property in the Category class. This represents a one-to-many relationship. A product can have one category, and a category can have many products.

One To Many Relationship

The following example shows an SQL query that retrieves the category related to each product from the Northwind database:

select productid, productname, p.categoryid, categoryname 
from products p 
inner join categories c on p.categoryid = c.categoryid

When this SQL is executed, the category information is included with each product:

Image

This is how that query can be executed by Dapper to map multiple products in one pass:

using (var connection = new SQLiteConnection(connString))
{
    var sql = @"select productid, productname, p.categoryid, categoryname 
                from products p 
                inner join categories c on p.categoryid = c.categoryid";
    var products = await connection.QueryAsync<Product, Category, Product>(sql, (product, category) => {
        product.Category = category;
        return product;
    }, 
    splitOn: "CategoryId" );
    products.ToList().ForEach(product => Console.WriteLine($"Product: {product.ProductName}, Category: {product.Category.CategoryName}"));
    Console.ReadLine();
}

When the code above is executed, the products are output along with their related categories:

Image

Multi mapping is implemented as a Func generic delegate. There are overloads to the Query and QueryAsync methods that take multiple generic parameters and a Func<TFirst, ..., TReturn> map argument. The last parameter represents the return type, while the others are input parameters to be processed within the body of the Func delegate, which is a mapping function. The mapping function specifies how the resulting data should be mapped to the return type.

In this example, the input parameters are Product and Category, and the return type is Product (<Product, Category, Product>). So we are telling the QueryAsync method to take a product, a category, process them in some way to be defined, and to return a product.

The mapping function in this instance is quite clear:

(product, category) => {
    product.Category = category;
    return product;
}

Dapper maps data to the first type in the same way as it does if only one generic parameter has been supplied to the QueryAsync<T> method. If is then told to map data to the Category type, and to assign the resulting object to the product's Category property.

How does Dapper identify a Category object from the data? Notice the splitOn argument? That tells Dapper to split the data on the CategoryId column. Anything up to that column maps to the first parameter (the Product), and anything else from that column onward should be mapped to the second input parameter (the Category).

Many To Many Relationships

Multiple mapping also works with many to many relationships. The following diagram shows a many-to-many relationship between the Post entity and a Tag entity in a model relating to a Blog application:

Many To Many Relationship

This is how the associations are represented in code:

public class Tag
{
    public int TagId { get; set; }
    public string TagName { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Headline { get; set; }
    public string Content { get; set; }
    public Author Author { get; set; }
    public List<Tag> Tags { get; set; } 
}

The Tag class has a collection property representing many posts, and the Post class includes a collection property representing many tags.

Many to many relationships are represented in database schema as a separate JOIN table:

Many To Many Relationship Join Table

The following SQL retrieves the tag information related to each post:

select p.postid, headline, t.tagid, tagname
from posts p 
inner join posttags pt on pt.postid = p.postid
inner join tags t on t.tagid = pt.tagid

A row is returned for each tag, resulting in duplication of post information:

Image

You use multi mapping to populate both entities in the same way as previously, but this time, you use a grouping function on the result to combine the duplicate post instances and the tags:

using (var connection = new SQLiteConnection(connString))
{
    var sql = @"select p.postid, headline, t.tagid, tagname
                from posts p 
                inner join posttags pt on pt.postid = p.postid
                inner join tags t on t.tagid = pt.tagid";
    var posts = await connection.QueryAsync<Post, Tag, Post>(sql, (post, tag) => {
        post.Tags.Add(tag);
        return post;
    }, splitOn: "tagid");
    var result = posts.GroupBy(p => p.PostId).Select(g =>
    {
        var groupedPost = g.First();
        groupedPost.Tags = g.Select(p => p.Tags.Single()).ToList();
        return groupedPost;
    });
    foreach(var post in result)
    {
        Console.Write($"{post.Headline}: ");
        foreach(var tag in post.Tags)
        {
            Console.Write($" {tag.TagName} ");
        }
        Console.Write(Environment.NewLine);
    }
}

Then the tags (highlighted in yellow) are output along with the post headline:

Image

Multiple Relationships

Multi mapping works with multiple relationships. In this example, the author details are included in the query to be executed against the database:

select p.postid, headline, firstname, lastname, t.tagid, tagname
from posts p 
inner join authors a on p.authorid = a.authorid
inner join posttags pt on pt.postid = p.postid
inner join tags t on t.tagid = pt.tagid

The multi mapping function takes an additional input parameter representing the extra entity being retrieved:

using (var connection = new SQLiteConnection(connString))
{
    var sql = @"select p.postid, headline, firstname, lastname, t.tagid, tagname
                from posts p 
                inner join authors a on p.authorid = a.authorid
                inner join posttags pt on pt.postid = p.postid
                inner join tags t on t.tagid = pt.tagid";
    var posts = await connection.QueryAsync<Post, Author, Tag, Post>(sql, (post, author, tag) => {
        post.Author = author;
        post.Tags.Add(tag);
        return post;
    }, splitOn: "firstname, tagid");
    var result = posts.GroupBy(p => p.PostId).Select(g =>
    {
        var groupedPost = g.First();
        groupedPost.Tags = g.Select(p => p.Tags.Single()).ToList();
        return groupedPost;
    });
    foreach(var post in result)
    {
        Console.Write($"{post.Headline}: ");
        foreach(var tag in post.Tags)
        {
            Console.Write($" {tag.TagName} ");
        }
        Console.Write($" by {post.Author.FirstName} {post.Author.LastName} {Environment.NewLine}");
    }
}

Image