Return nested objects using Dapper

Key here is to craft the query so that the nested object properties appear at the end of the result set, with the key of the nested object marking the boundary between the parent and the nested object.

public class RequestLog
{
    public int UserId { get; set; }
    public DateTime TimeStamp { get; set; }
    public string Path { get; set; }
    public string Form { get; set; }
    public string Query { get; set; }
    public string Method {  get; set; }
    public User User { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
}

Nested object in the example above is the RequestLog's User property. So we want the RequestLog properties first, followed by those that form the User:

select  
    [timestamp], 
    [path], 
    form, 
    query, 
    method, 
    row_number() over (partition by l.userid order by timestamp desc) as rownumber,
    u.userid,
    u.name
from RequestLogs l 
inner join users u on l.userid = u.userid	

The Dapper QueryAsync method optionally takes generic parameters representing the type of the return value, but also the types represented by each row that we can pass to a map function to create the return value. Here, for each row, we want to return a complete RequestLog constructed from a RequestLog and a User, so we specify the RequestLog and User as generic input parameters and then pass them as parameters to the map Func where we assign the hydrated user to the Requestlog User property and return the full RequestLog object. We also need to let Dapper know that the userid marks the boundary between the requestlog and the user (splitOn):

var data = await conn.QueryAsync<RequestLog, User, RequestLog>(sql, map:(requestlog, user) => { 
            requestlog.User = user; 
            return requestlog; 
        }, param: new { number }, splitOn:"userid");

The map function is executed for each row of data returned from the database. The splitOn parameter is not needed if the boundary marker is called Id.

Multiple object example:

public async Task<List<FormMailing>> GetMailingsForFormAsync(int formId, string filter, int skip = 0, int take = 40)
{
    var sql = @"; with cte as 
                (
                    SELECT [fm].[FormMailingId], [fm].[FormId], [c0].[Test], m.MailingId, [f1].[Domain],  [m].[DateSent], 
                    [m].[CampaignId], [fm].[CompanyId], [c].[CompanyName], [c1].[ContactId], [c1].[FirstName], 
                    [c1].[LastName], [c1].[EmailAddress], ROW_NUMBER() OVER (partition by [c1].[ContactId] ORDER BY [m].[DateSent] DESC) AS RowNumber
                    FROM FormMailings as [fm] 
                    INNER JOIN [Mailings] AS [m] ON [fm].[MailingId] = [m].[MailingId]
                    INNER JOIN [Campaigns] AS [c0] ON [m].[CampaignId] = [c0].[CampaignId]
                    INNER JOIN [Forms] AS [f0] ON [fm].[FormId] = [f0].[FormId]
                    INNER JOIN [FormTypes] AS [f1] ON [f0].[FormTypeId] = [f1].[FormTypeId]
                    INNER JOIN [Contacts] AS [c1] ON [fm].[ContactId] = [c1].[ContactId]
                    INNER JOIN [Companies] AS [c] ON [fm].[CompanyId] = [c].[CompanyId]
                    WHERE [fm].[FormId] = @formId and 
                    (@filter is null or [c].[CompanyName] like'%'+@filter+'%')
                )select * from CTE where RowNumber = 1 ORDER BY [CompanyName] OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY";

    var result = await context.Database.GetDbConnection().QueryAsync<FormMailing, Mailing, Company, Contact, FormMailing>(sql, map: (fm, mailing, company, contact) =>
    {
        fm.Mailing = mailing;
        fm.Company = company;
        fm.Contact = contact;
        return fm;
    }, new{formId, filter, skip, take}, splitOn: "MailingId, CompanyId, ContactId");
    return result.AsList();
}

The generic parameter list represents the input types followed by the output type. The splitOn parameter takes a comma separated list of the column names to be used as type separators.

Collections

When returning an item and its collection, use GroupBy on the returned rows:

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");

    // at this point, we have duplicate post rows, each one with its own tag info
    // group the rows by post and add each tag to the first result	
    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} ");
        }
    }