MVC populate model with hierarchy

Question

I have a model: List<PROJECT>

PROJECT -> having List<TASK>

TASK-> Having List<SUBTASK>

Data will be populated from Project,Task,Subtask tables respectively with reference to each.

What is the efficient way of populating This model with pagination?

What I'm implementing:

1. Select Project (first procedure call returns 10 project records(displaying 10 records ))
2. Select List of tasks based on ProjectID(second procedure called 10 times to bring task list under projects)
3. Select List of subtasks based on TaskID (third procedure called multiple times to bring subtask list under tasks)

This solution is working fine but require multiple database calls.

Update

 public List<Project> GetProject(int page=1, int pageSize=10)
    {
        List<Project> _lstProject = new List<Project>();
        try
        {
            using (DevEntities db = new DevEntities())
            {
                _lstProject = db.ProjectSelect(page, pageSize).Select(m => new Project()
                {
                    ProjectId = m.ProjectID,
                    ProjectNumber = m.ProjectNo,
                    TaskList = GetTaskDetails(m.ProjectID)

                }).ToList();

            }
            return _lstProject ;
        }
        catch (Exception e)
        {
            throw;
        }

        finally
        {
            _lstProject = null;
        }
    }

public List<Task> GetTaskDetails(ProjectID)
    {
        List<Task> _lstTask = new List<Task>();
        try
        {
            using (DevEntities db = new DevEntities())
            {
                _lstTask = db.TaskSelect(ProjectID).Select(m => new Task()
                {
                    TaskId = m.TaskID,
                    TaskNumber = m.TaskNo,
                    SubTaskList = GetSubTaskDetails(m.TaskID)

                }).ToList();

            }
            return _lstTask ;
        }
        catch (Exception e)
        {
            throw;
        }

        finally
        {
            _lstTask = null;
        }
    }

public List<SubTask> GetSubTaskDetails(int TaskID)
    {
        List<SubTask> _lstSubTask = new List<SubTask>();
        try
        {
            using (DevEntities db = new DevEntities())
            {
                _lstSubTask = db.TaskSelect(TaskID).Select(m => new SubTask()
                {
                    SubTaskId = m.SubTaskID,
                    SubTaskNumber = m.SubTaskNo
                }).ToList();

            }
            return _lstSubTask ;
        }
        catch (Exception e)
        {
            throw;
        }

        finally
        {
            _lstSubTask = null;
        }
    }

Answer 1

In my opinion, in order to optimize your code you have two valid options:

Option 1:

One query for each entity type (total 3 queries):

var projects = db.Projects.Where().Order().Skip().Take().Select().ToList(); // 1 query
var projectIds = projects.Select(x => x.ProjectId).ToList();

var tasks = db.Tasks.Where(x => projectIds.Contains(x.ProjectId)).Select().ToList(); // 1 query
var taskIds = tasks.Select(x => x.Id).ToList();

var subtasks = db.Tasks.Where(x => taskIds.Contains(x.TaskId)).Select().ToList() // 1 query

foreach(var project in projects)
{
    project.Tasks = tasks.Where(x => x.ProjectId == project.ProjectId).ToList();

    // etc
    // complete hierarchy structure
}

Option 2:

One query with 2 left outer joins:

var projects = (from proj in db.Projects.Where()

                join t in db.Tasks on t.ProjectId equals proj.ProjectId into tasks
                from task in t.DefaultIfEmpty()

                join s in db.Tasks on s.TaskId equals task.Id into subtasks
                from subtask in subtasks.DefaultIfEmpty()

                select new 
                {
                    ProjectId = proj.ProjectId,
                    TaskId = task.Id,
                    SubtaskId = subtask.Id

                }).ToList(); // 1 query

// etc
// proceed with creating hierarchy structure using GroupBy

Which one of these two is more efficient ?

It depends. I'm sure there are a lot of people which prefer one over the other and i would actually be happy to hear their comments.

My personal experience taught me to usually choose Option 1, but you should test and time both of them based on your data volume and db configuration (indexes, fks) and decide for yourself