LINQ2SQL: Join multiple tables

Question

I'm not that good at linq2sql or even sql, but everything comes with a little training.

My question: i got 3 tables: Projects, Folders and Tasks.

I need to make the query so that i join these 3 tables.

I've tried a bit:

public ActionResult Details(string id) {
        var user = GetProfile().Id;
        var p = _db.Projects.Where(x => Convert.ToString(x.ProjectId) == id && x.UserId == user).SingleOrDefault();
        var f = _db.Folders.Where(x => x.ProjectId == p.ProjectId).ToList();
        return View(f);
    }

This works fine, and i get the folders related to the project. Now i want, in the same query the tasks, who is related to the folders and the project.

So at the end i get this scenario: Click's on a projectname, sends the ID, takes the ID, and presents the folders related to the project i clicked on and presents the tasks on the same site as the folders.

Answer 1

I'm not completely sure on what you're trying to accomplish exactly here. But here is a linq to sql query where I am joining projects onto folders and folders onto tasks. Project has a field named ProjectId and Folders also has a "ProjectId" in which I'm joining on. Folders will have a "FolderId" and tasks will also have a "FolderId", assuming the same naming convention. Hope it helps:

var result = (from projects in _db.Projects
                      join folders in _db.Folders
                      on projects.ProjectId equals folders.ProjectId
                      join tasks in _db.Tasks
                      on folders.FolderId equals tasks.FolderId
                      where projects.Id.ToString() == id
                      && projects.UserId == user
                      select projects).ToList();

result will represent a list of Projects that match the criteria.