I want to be able to select a product and retrieve all sub-parts(products) within it. My approach is to find the Product id and then retrieve the list of ProductParts with that as a parent and while fetching those, follow the key back to the Product child to get the name and details of each Part. I was hoping to use something similar to: part.linked_product_id.name

I have two tables. One for [Product] and and a relation [ProductPart] that has two FK ID's to [Product]

Table Product()
{
    int ID; // (PRIMARY, NOT NULL)
    String Name;

    ... details removed for overview purpose...
}


Table ProductPart()
{
    int Product_ID;        // FK (linked with relation to Product/parent) 
    int Part_Product_ID;   // FK (linked with relation to Product/childen)

    ... details removed for overview purpose...
}

I have checked the SQL-diagram and it shows the two relations (both are one to many) and in my DBML they also looks right.

Here is my LINQ to SQL snippet that doesnt work for me... wondering why my joins dont work as supposed.

FaultySnippet()
{ 

   ProductDataContext db = new ProductDataContext();

   var list =
   (   
       from part in db.ProductParts
       join prod in db.Products on part.Part_Product_ID equals prod.ID 
       where (part.Product_ID == product_ID)

       select new 
       {
            Name = part.Part_Product_ID. ?? // <-- NO details from Joined table?

            ... rest of properties from ProductPart join... I hoped...
       }
   );
}