.NET Core中如何用linq2db单查询填充父列表的子列表？

需要查询数据库返回包含附件的交接班报告（TurnReport），要求按日期返回交接班报告项列表，每个报告需附带所有关联附件，两者仅通过EntryId关联，且希望通过单次数据库连接完成查询。

现有实现代码 #

带附件子查询的报告获取方法（存在问题） #

public List<TurnReportItem> GetTurnReportsByDateShiftAndDept(DateTime shiftStart, int shiftNum, int dept)
{
    try
    {
        List<TurnReportItem> list;
        using (connection)
        {
            list = (from r in connection.VTurnReports
                    join a in connection.TurnReportAreas on r.AreaId equals a.AreaId
                    where a.DeptId == dept && a.Enabled && r.ShiftDate == shiftStart && r.ShiftNum == shiftNum
                    select new TurnReportItem 
                    { 
                        areaId = r.AreaId, 
                        areaName = a.Name, 
                        author = r.Author, 
                        comment = r.Comment, 
                        datetime = r.Datetime, 
                        id = r.EntryId, 
                        ip = r.Ip, 
                        shiftDate = r.ShiftDate, 
                        shiftNum = r.ShiftNum, 
                        sort_order = a.SortOrder, 
                        attachment_count = r.AttachmentCount, 
                        attachments = (
                            from at in connection.TurnReportAttachments where at.EntryId == r.EntryId
                            select new TurnReportAttachment
                            {
                                AttachmentId = at.AttachmentId,
                                FileName = at.FileName
                            }).ToList()
                    })
                   .OrderBy(r => r.sort_order)
                   .OrderBy(r => r.datetime)
                   .ToList();
            return list;
        }
    }
    // 原代码缺少catch块，建议补充异常处理逻辑
}

TurnReportItem实体类 #

public class TurnReportItem
{
    public int id;
    public string comment;
    public DateTime datetime;
    public string author;
    public int areaId;
    public string areaName;
    public DateTime shiftDate;
    public int shiftNum;
    public string ip;
    public int? attachment_count;
    public int sort_order;
    public int area_rating;
    public List<TurnReportAttachment> attachments;

    public TurnReportItem() { }
}

独立获取附件的方法（当前项目无法生效） #

List<TurnReportItem> list;
using (connection)
{
    list = (from r in connection.VTurnReports
            join a in connection.TurnReportAreas on r.AreaId equals a.AreaId
            where a.DeptId == dept && a.Enabled && r.ShiftDate == shiftStart && r.ShiftNum == shiftNum
            select new TurnReportItem 
            { 
                areaId = r.AreaId, 
                areaName = a.Name, 
                author = r.Author, 
                comment = r.Comment, 
                datetime = r.Datetime, 
                id = r.EntryId, 
                ip = r.Ip, 
                shiftDate = r.ShiftDate, 
                shiftNum = r.ShiftNum, 
                sort_order = a.SortOrder, 
                attachment_count = r.AttachmentCount, 
                attachments = SelectAttachmentsByEntryId(r.EntryId)
            })
           .OrderBy(r => r.sort_order)
           .OrderBy(r => r.datetime)
           .ToList();
    return list;
}

public List<TurnReportAttachment> SelectAttachmentsByEntryId(int EntryId)
{
    using (connection)
    {
        // 不返回二进制数据，只取必要字段
        var results = from p in connection.TurnReportAttachments
                      where p.EntryId == EntryId
                      select new TurnReportAttachment
                      {
                          EntryId = p.EntryId,
                          AttachmentId = p.AttachmentId,
                          FileName = p.FileName
                      };

        return results.ToList();
    }
}

解决方案：单次连接完成报告与附件关联查询 #

问题根源 #

1. 第一种方法的子查询会被ORM解析为N+1查询（每个报告单独查一次附件），或因ORM限制无法正确转换为SQL。
2. 第二种方法在LINQ查询中调用外部方法，ORM无法将其转换为SQL，且内部重复使用connection会引发连接状态冲突。

优化实现代码 #

public List<TurnReportItem> GetTurnReportsByDateShiftAndDept(DateTime shiftStart, int shiftNum, int dept)
{
    try
    {
        using (var connection = new YourDbConnectionType()) // 替换为实际数据库连接类型
        {
            connection.Open();
            
            // 1. 查询所有符合条件的交接班报告
            var reports = (from r in connection.VTurnReports
                           join a in connection.TurnReportAreas on r.AreaId equals a.AreaId
                           where a.DeptId == dept && a.Enabled && r.ShiftDate == shiftStart && r.ShiftNum == shiftNum
                           select new TurnReportItem 
                           { 
                               areaId = r.AreaId, 
                               areaName = a.Name, 
                               author = r.Author, 
                               comment = r.Comment, 
                               datetime = r.Datetime, 
                               id = r.EntryId, 
                               ip = r.Ip, 
                               shiftDate = r.ShiftDate, 
                               shiftNum = r.ShiftNum, 
                               sort_order = a.SortOrder, 
                               attachment_count = r.AttachmentCount,
                               attachments = new List<TurnReportAttachment>() // 初始化空附件列表
                           })
                          .OrderBy(r => r.sort_order)
                          .OrderBy(r => r.datetime)
                          .ToList();
            
            // 2. 批量查询所有关联附件（单次数据库请求）
            var reportIds = reports.Select(r => r.id).ToList();
            var attachments = (from at in connection.TurnReportAttachments
                               where reportIds.Contains(at.EntryId)
                               select new TurnReportAttachment
                               {
                                   AttachmentId = at.AttachmentId,
                                   FileName = at.FileName,
                                   EntryId = at.EntryId
                               }).ToList();
            
            // 3. 内存中关联报告与附件
            foreach (var report in reports)
            {
                report.attachments = attachments.Where(at => at.EntryId == report.id).ToList();
            }
            
            return reports;
        }
    }
    catch (Exception ex)
    {
        // 添加日志记录等异常处理逻辑
        throw;
    }
}

优化说明 #

• 仅两次数据库查询（报告+附件），均在同一个连接中完成，彻底避免N+1问题。
• 内存关联附件的方式性能优于逐个查询，且规避了ORM对子查询的解析限制。
• 确保连接资源被正确释放，避免连接泄漏。

内容的提问来源于stack exchange，提问作者Avi4nFLu