Self Referencing Many-To-Many Linq Query

Question

I have a datatable like this:

MtoM
{
   ParentID, 
   ChildID
}

Item
{
    ID,
    Data,
    Label
}

How do I write a linq query that returns every ChildID under a given ParentID and the associated Data and Label for each of these decendent IDs. If I were using SQL I'd use a union all and inner join, but I don't know linq well enough to do this.

Performance is absolutely not an issue as there will be at most 3 levels of nesting and only 1 or 2 items in each level. The DDL I'm trying to populate is rarely used and is not mission critical.

Answer 1

assuming that child id is refering ID field in items table u can write following query to fetch required records

from mt in MToM 
where mt.ParentID == GivenParentID
join it in Item on mt.ChildId equals it.ID
select new { parentID = mt.ParentID, childID = it.ID, childData = it.Data, childLabel = it.Label}

data is being returned in anonymous type. u can create new Type and populate it with resultant records