Join multilevel relationship in LINQ to SQL

Question

There are 3 tables: ParentCategories -> Categories -> Articles.

• ParentCategory(ID, Name)
• Category(ID, parentCategoryID, Name)
• Article(ID, caregoryID, Name)

How do I select all articles with specified parentCategoryID (the table articles has only reference to categoreID, not to ParentCategoryID) using LINQ to SQL?

Something like this:

articles = (
    from a in db.Articles
    join c in db.Categories
    on ????????????
    join pc in db.ParentCategories 
    on c.ParentCategoryId equals pc.ID
    ...);

Answer 1

articles = from a in db.Articles
          join c in db.Categories
          on myParentCategoryID equals c.ParentCategoryId
select a;

Answer 2

(If I understand your schema correctly) you could use an implicit join strategy like:

var articles = db.Categories
    .Where(c => c.ParentCategoryID == yourParentCategoryID)
    .SelectMany(c => c.Articles)
    .ToList();

The implicit join requires that you have Associations set up between your entities in your O/R designer.