LINQ Query - Complicated Self Join Problems

Question

So I have Projects (for this example say they are a Name and an ID). I also have a table called SubProjects, like this:

MasterProjectID       SubProjectID
1                     2
1                     3
4                     5
4                     6
4                     7

A master cannot be a sub of another master.

I want to return a list of project IDs. Specifically, I want the list to contain the Master project ID and all of its Sub project IDs.

In other words, if projectID == 4 and pdc is my DataContext, my query should return:

4
5
6
7

The following linq query returns nothing:

   from j in pdc.Projects
   join s in pdc.SubProjects on j.ProjectID equals s.SubProjectID
   where j.ProjectID.Equals(projectID) || s.MasterProjectID.Equals(projectID)
   select j.ProjectID;

What am I doing wrong?

Answer 1

You could retrieve a list of sub-projects simply with:

var query = from j in pdc.SubProjects
            where j.MasterProjectID == projectID
            select j.SubProjectID;

... Then manually append/prepend the known value of projectID.

var list = query.ToList();
list.Add(projectID);

Answer 2

got a question.

In edmx (ADO entity) I select a table. The table is self join.
you have the ChildID and the ParentID.

So evrey Child record has parentID. If parentID = NULL then it's highest level.

when I do datacontext.Table1.where( a=> a. )...... the parent ID is not prompted. I can't use parentID

e.g. I like to do datacontext.Table1.where( a=> a.parentid == 4 ).

Any why parentid is not displayed how can I achieve this? I also don't see the something like childs.....

please advice...