Dapper - Split Queries

Example: Mapping Two Objects with splitOn

If you need to join the output of two tables and map the result to two objects then you can use splitOn which instructs Dapper to start mapping the second object at ther second Id column.

You can now hydrate multiple objects without requiring additional mapping code.

var sql = @"
SELECT
    u.""Id"", u.""Name"",
    p.""Id"", p.""Title"", p.""UserId""
FROM ""Users"" u
LEFT JOIN ""Posts"" p ON p.""UserId"" = u.""Id""
ORDER BY u.""Id"", p.""Id"";
";

var rows = await _connection.OpenAsync<User, Post, (User, Post)>((
    sql,
    (u, p) => (u, p),
    splitOn: "Id"
);

return rows.ToList();

Example: Mapping Three Objects with splitOn

You can also map more than two objects from a single query result by specifying multiple types and using the splitOn parameter to indicate where each mapping should start. For example, if you want to join Users, Posts, and Comments, you can do the following:

var sql = @"
SELECT
    u.""Id"", u.""Name"",
    p.""Id"", p.""Title"", p.""UserId"",
    c.""Id"", c.""Content"", c.""PostId""
FROM ""Users"" u
LEFT JOIN ""Posts"" p ON p.""UserId"" = u.""Id""
LEFT JOIN ""Comments"" c ON c.""PostId"" = p.""Id""
ORDER BY u.""Id"", p.""Id"", c.""Id"";
";

var rows = await _connection.QueryAsync<User, Post, Comment, (User, Post, Comment)>(
    sql,
    (u, p, c) => (u, p, c),
    splitOn: "Id,Id"
);

return rows.ToList();

In this example, Dapper will map the first set of columns to User, the next set (starting at the second Id) to Post, and the final set (starting at the third Id) to Comment. The splitOn parameter tells Dapper where each object's mapping should begin in the result set.