如何用Dapper将多对象映射为单对象？处理EmailAttached为空场景

问题背景 #

我想找个最优方案把查询结果映射到单个EmailDto对象，用Dapper本来应该很顺畅，但有个问题——EmailAttached可能为空。

EmailDto结构：

public class EmailDto
{
    public Guid EmailId { get; set; }
    public Guid EmailAccountId { get; set; }
    public string EmailName { get; set; }
    public string Subject { get; set; }
    public string Content { get; set; }
    public string Receiver { get; set; }
    public EmailAccountDto EmailAccount { get; set; }
    public List<EmailAttachedDto> EmailAttached { get; set; }
}

对应的查询语句：

var sqlQuery = "SELECT " +
                $"Email.EmailId AS {nameof(EmailDto.EmailId)}, " +
                $"Email.EmailAccountId AS {nameof(EmailDto.EmailAccountId)}, " +
                $"Email.EmailName AS {nameof(EmailDto.EmailName)}, " +
                $"Email.Subject AS {nameof(EmailDto.Subject)}, " +
                $"Email.Content AS {nameof(EmailDto.Content)}, " +
                $"Email.Receiver AS {nameof(EmailDto.Receiver)}, " +
                $"EmailAttached.EmailAttachedId AS {nameof(EmailAttachedDto.EmailAttachedId)}, " +
                $"EmailAttached.EmailId AS {nameof(EmailAttachedDto.EmailId)}, " +
                $"EmailAttached.FilePath AS {nameof(EmailAttachedDto.FilePath)}, " +
                $"EmailAttached.Name AS {nameof(EmailAttachedDto.Name)}, " +
                $"EmailAccount.EmailAccountId AS {nameof(EmailAccountDto.EmailAccountId)}, " +
                $"EmailAccount.Name AS {nameof(EmailAccountDto.Name)}, " +
                $"EmailAccount.EmailAddress AS {nameof(EmailAccountDto.EmailAddress)}, " +
                $"EmailAccount.SmtpServer AS {nameof(EmailAccountDto.SmtpServer)}, " +
                $"EmailAccount.SmtpPort AS {nameof(EmailAccountDto.SmtpPort)}, " +
                $"EmailAccount.SmtpLogin AS {nameof(EmailAccountDto.SmtpLogin)}, " +
                $"EmailAccount.SmtpPassword AS {nameof(EmailAccountDto.SmtpPassword)} " +
                $"FROM Commander_Email AS Email " +
                $"LEFT JOIN Commander_EmailAccount AS EmailAccount ON Email.EmailAccountId = EmailAccount.EmailAccountId " +
                $"LEFT JOIN Commander_EmailAttached AS EmailAttached ON Email.EmailId = EmailAttached.EmailId " +
                "WHERE Email.ProcessedDate IS NULL";

我已尝试过dynamic emailJobs = await connection.QueryAsync<dynamic>(sqlQuery);，但需要映射到EmailDto对象。

方案一：单查询+Dapper多映射分组 #

由于LEFT JOIN会返回重复的Email和EmailAccount数据（每个附件对应一行），可以用字典分组去重，同时处理空附件的情况：

var emailDict = new Dictionary<Guid, EmailDto>();

var result = await connection.QueryAsync<EmailDto, EmailAccountDto, EmailAttachedDto, EmailDto>(
    sqlQuery,
    (email, account, attached) => 
    {
        // 复用同一EmailId的EmailDto实例
        if (!emailDict.TryGetValue(email.EmailId, out var existingEmail))
        {
            existingEmail = email;
            existingEmail.EmailAccount = account;
            existingEmail.EmailAttached = new List<EmailAttachedDto>();
            emailDict.Add(existingEmail.EmailId, existingEmail);
        }
        
        // 仅添加有效附件（空附件时LEFT JOIN返回的attached字段为null或主键为空）
        if (attached != null && attached.EmailAttachedId != Guid.Empty)
        {
            existingEmail.EmailAttached.Add(attached);
        }
        
        return existingEmail;
    },
    splitOn: "EmailAccountId,EmailAttachedId" // 指定拆分字段，告诉Dapper如何区分不同对象的列
);

// 最终去重后的EmailDto列表
var emailDtos = emailDict.Values.ToList();

关键说明：

• splitOn参数：按顺序指定拆分字段，Dapper会从左到右拆分出EmailDto（到第一个split字段前）、EmailAccountDto（第一个到第二个split字段间）、EmailAttachedDto（第二个split字段后）。
• 字典分组：避免重复创建相同EmailId的EmailDto和EmailAccountDto实例。
• 空附件判断：通过附件主键（EmailAttachedId）是否为空来过滤无效的空附件数据。

方案二：拆分查询+手动关联 #

如果不想处理重复行，可以拆分为两次查询，分别获取主数据和附件，再手动关联：

// 1. 查询Email和关联的EmailAccount
var emailSql = "SELECT " +
               $"Email.EmailId AS {nameof(EmailDto.EmailId)}, " +
               $"Email.EmailAccountId AS {nameof(EmailDto.EmailAccountId)}, " +
               $"Email.EmailName AS {nameof(EmailDto.EmailName)}, " +
               $"Email.Subject AS {nameof(EmailDto.Subject)}, " +
               $"Email.Content AS {nameof(EmailDto.Content)}, " +
               $"Email.Receiver AS {nameof(EmailDto.Receiver)}, " +
               $"EmailAccount.EmailAccountId AS {nameof(EmailAccountDto.EmailAccountId)}, " +
               $"EmailAccount.Name AS {nameof(EmailAccountDto.Name)}, " +
               $"EmailAccount.EmailAddress AS {nameof(EmailAccountDto.EmailAddress)}, " +
               $"EmailAccount.SmtpServer AS {nameof(EmailAccountDto.SmtpServer)}, " +
               $"EmailAccount.SmtpPort AS {nameof(EmailAccountDto.SmtpPort)}, " +
               $"EmailAccount.SmtpLogin AS {nameof(EmailAccountDto.SmtpLogin)}, " +
               $"EmailAccount.SmtpPassword AS {nameof(EmailAccountDto.SmtpPassword)} " +
               $"FROM Commander_Email AS Email " +
               $"LEFT JOIN Commander_EmailAccount AS EmailAccount ON Email.EmailAccountId = EmailAccount.EmailAccountId " +
               "WHERE Email.ProcessedDate IS NULL";

var emails = await connection.QueryAsync<EmailDto, EmailAccountDto, EmailDto>(
    emailSql,
    (email, account) => 
    {
        email.EmailAccount = account;
        email.EmailAttached = new List<EmailAttachedDto>();
        return email;
    },
    splitOn: "EmailAccountId"
);

// 2. 查询所有关联的附件
var attachedSql = "SELECT " +
                  $"EmailAttachedId AS {nameof(EmailAttachedDto.EmailAttachedId)}, " +
                  $"EmailId AS {nameof(EmailAttachedDto.EmailId)}, " +
                  $"FilePath AS {nameof(EmailAttachedDto.FilePath)}, " +
                  $"Name AS {nameof(EmailAttachedDto.Name)} " +
                  $"FROM Commander_EmailAttached " +
                  $"WHERE EmailId IN @EmailIds";

var attachments = await connection.QueryAsync<EmailAttachedDto>(attachedSql, new { EmailIds = emails.Select(e => e.EmailId) });

// 3. 关联附件到对应的Email
foreach (var email in emails)
{
    email.EmailAttached.AddRange(attachments.Where(a => a.EmailId == email.EmailId));
}

关键说明：

• 避免了重复行问题，适合附件数据量较大的场景。
• 两次查询的性能开销通常远小于处理大量重复行的开销。

内容的提问来源于stack exchange，提问作者Guinea Pig Taco