How do I fill a child list inside a parent list using linq2db in a single query in my .net core app?

Question

I am trying to query my database to return turn reports with any attachments included. I need a list of turn report items which are returned by date, and then for each report I want it to also return all of the attachments associated with the turn reports. The only way to associate them is by the EntryId.

Here is my method to get the turn reports:

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;
        }
    }

Here is the TurnReportItem class that I am filling. If I do not have the subquery I do get all of the turnreports.

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() { }
}

I have a separate method that will return the all of the comments with the entry id. I have tried to fill the list using that method. I am converting this from a MVC app and I was able to use the method to fill the list however it will not work when I try it in this app, I also would prefer to only make one connection in the database to get what I need.

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)
    {
        // we do it this way so that we don't return the blob
        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();
    }
}

`using (connection)` dispose connection. Remove it from SelectAttachmentsByEntryId. — Alexander Petrov, Feb 18 '23 at 05:06

score 0 · Answer 1 · answered Feb 19 '23 at 13:10

In your case SelectAttachmentsByEntryId should be static, with additional parameter connection. To make it work, it is needed to use ExpressionMethod.

public static class ReportHelpers
{
    [ExpressionMethod(nameof(SelectAttachmentsByEntryIdImpl))]
    public static List<TurnReportAttachment> SelectAttachmentsByEntryId(MyConnection connection, int EntryId)
    {
        throw new InvalidOperationException(); // should never enter here
    }

    private static Expression<Func<MyConnection, int, List<TurnReportAttachment>>> SelectAttachmentsByEntryIdImpl()
    {
        return (connection, EntryId) =>
            (from p in connection.TurnReportAttachments
            where p.EntryId == EntryId
            select new TurnReportAttachment
            {
                EntryId = p.EntryId,
                AttachmentId = p.AttachmentId,
                FileName = p.FileName
            })
            .ToList();
    }    
}

Then you can use this method in queries:

public List<TurnReportItem> GetTurnReportsByDateShiftAndDept(DateTime shiftStart, int shiftNum, int dept)
{
    using (connection)
    {
        var 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 = ReportHelpers.SelectAttachmentsByEntryId(connection, r.EntryId)
                })
                .OrderBy(r => r.sort_order)
                .ThenBy(r => r.datetime)
                .ToList();
        return list;
    }
 }

Note that OrderBy.OrderBy has no sense. It should be OrderBy.ThenBy

How do I fill a child list inside a parent list using linq2db in a single query in my .net core app?

1 Answers1