Dapper Nested Object mapping

In the past years I have used ORM (Object Relational Mapping) tools, such as Entity Framework, a lot. But recently started to use Dapper in a daily basis. For simple queries it’s really easy to implement it and it’s really fast. After all, the implementation as same as plain ADO.Net implementation only more easy and flexible.

It’s a trivial task to retrieve nested objects with a single query when using Entity Framework. To perform the same task with Dapper is a little bit tricky but still not impossible.

Let’s think about having a 2 tables called Posts and Categories as follow;

List of Posts with Category name requires a n-1 relation between those tables. Nested objects within n-1 or 1-1 relations more easy to retrieve;

using (var conn = new SqlConnection(_connectionString))
{
    var query = @"SELECT P.Id
                        , P.Title
                        , P.Content
                        , C.Id
                        , C.Name
                    FROM Posts AS P
                    INNER JOIN Categories AS C on P.CategoryId = C.Id";

    var result = await conn.QueryAsync<Post, Category, Post>(query, (p, c) =>
    {
        p.Category = c;
        return p;
    });

    return result.AsList();
}

Let’s make it a little bit more challenging and assume that, we want to retrieve list of Categories with related Posts as nested collection. The relationship between tables 1-n which requires additional lookup dictionary. Otherwise it will resulted with duplication.

using (var conn = new SqlConnection(_connectionString))
{
    var query = @"SELECT C.Id
	                , C.Name
	                , P.Id
	                , P.Title
	                , P.Content
                FROM Categories AS C
                INNER JOIN Posts AS P on P.CategoryId = C.Id";

    var lookup = new Dictionary<int, Category>();

    var result = await conn.QueryAsync<Category, Post, Category>(query, (c, p) => 
    {
        if (!lookup.TryGetValue(c.Id, out Category category))
            lookup.Add(c.Id, category = c);

        if (category.Posts == null)
            category.Posts = new List<Post>();

        category.Posts.Add(p);

        return category;
    });

    return result.AsList();
}