I’m having some difficulties expanding the provided example “One-To-Many” on https://www.learndapper.com/relationships

My use case is as follows, on a blazor SSR page I need to retrieve a single project including nested objects based on a ProjectID provided as a [Parameter] on this page.

The complete structure I need to retrieve looks like this:

Project
ProjectFase
ProjectItem
ProjectItemDate
Appointment

A single Project contains no, 1 or more ProjectFase
A single ProjectFase contains no, 1 or more ProjectItem
A single ProjectItem contains no, 1 or more ProjectItemDate
A single ProjectItemDate contains no, 1 or more Appointment

The Project class looks like this (I removed some fields for clarity):

public class Project
{
    public string ID { get; set; } //PK

    public List<ProjectFase>? Fases { get; set; } = new List<ProjectFase>();
}

public class ProjectFase
{
    public int ID { get; set; } //PK

    public string ProjectID { get; set; } //FK

    public List<ProjectItem>? Items { get; set; } = new List<ProjectItem> { };
}

public class ProjectItem
{
    public int ID { get; set; } //PK

    public int? ProjectFaseID { get; set; } //FK

    public List<ProjectItemDate>? Dates { get; set; } = new List<ProjectItemDate> { };
}

public class ProjectItemDate
{
    public int AppointmentID { get; set; } //PK, FK

    public int? ProjectItemID { get; set; } //FK

    public List<Appointment>? Appointments { get; set; } = new List<Appointment> { };
}

public class Appointment
{
    public int ID { get; set; } //PK

    public DateTime StartDate { get; set; }

    public DateTime EndDate { get; set; }

}

On the project page I retrieve the project by:

    [Parameter]
    public string ProjectID { get; set; }

    protected override async Task OnInitializedAsync()
    {

        project = await _db.GetProjectById<Project, dynamic>(ProjectID, new { projectid =    "projectid", faseid = "faseid" });

    }

GetProjectById is a method inside my DataAccess layer, and this method looks like this:

public async Task<Project> GetProjectById<T, U>(string ProjectID, U parameters)
{
    string connectionString = _config.GetConnectionString(ConnectionStringName);
    string sql = "select p.ID as projectid, f.ID as faseid, f.Name as fasename from dbo.Projects p INNER JOIN dbo.ProjectFase f ON p.ID = f.ProjectID WHERE p.ID  = '" + ProjectID + "' ";

    using (IDbConnection connection = new SqlConnection(connectionString))
    {
        try 
        {
            var projects = await connection.QueryAsync<Project, ProjectFase, Project>(sql, (project, fase) =>
            {
                project.Fases.Add(fase);
                return project;

            }, splitOn: "faseid");

            var result = projects.GroupBy(p => p.ID).Select(g =>
            {
                var groupedProject = g.First();
                groupedProject.Fases = g.Select(p => p.Fases.Single()).ToList();

                return groupedProject;  
            });

            return result.FirstOrDefault();

        }
        catch(Exception ex) 
        {
            
        }

        return null;
    }
}

This all works like a charm and i’m able to retrieve a single project object with multiple fases, but now I want to add the next levels (ProjectItem, ProjectItemDate, Appointment) to this object but can’t seem to get it right, how can I achieve this?

Note: I was able to retrieve the desired result by using a lot of foreach loops per object/level but given the amount of data this look 4-5 seconds on big projects so this approach won’t work for me.

I could really use some pointers here