SQL Query Conversion to LINQ Left Outer Join (VB.NET)

Question

I've looked all around and spent way to long trying to convert this SQL statement into a Linq statement in VB. I'm sure it would be a good example for others out there - the statement is trying to pull products that have a many-to-many relationship with product categories, and the categories have a hierarchy of parents/children.

Here is the query I am trying to convert:

SELECT  P.ProductID, P.ProductName, P.ProductSlug, P.PartNumber 
FROM         Products AS P 
INNER JOIN Products_Categories AS PC ON PC.ProductID = P.ProductID 
INNER JOIN Categories AS C ON PC.CategoryID = C.CategoryID 
LEFT OUTER JOIN Categories AS P_Cats ON P_Cats.CategoryID = C.Parent
WHERE     (C.CategoryID = 9) OR (C.Parent = 9) OR (P_Cats.Parent = 9)

I can get up to the point where I am trying to say "WHERE ... (P_Cats.Parent = 9)" but can't figure that part out.

THANKS!

Answer 1

Figured it out right after posting the question, but here is the answer in case anyone else finds it helpful:

Dim query = (From products In db.Products _
                Join PC In db.Products_Categories On PC.ProductID Equals products.ProductID _
                Join C In db.Categories On PC.CategoryID Equals C.CategoryID _
                Group Join cat In db.Categories On cat.CategoryID Equals C.Parent Into C_Parents = Group _
                From cParents In C_Parents.DefaultIfEmpty() _
                Where (C.CategoryID = categoryID Or C.Parent = categoryID Or cParents.CategoryID = categoryID) _
                And products.IsDeleted = False)

It was the line "From cParents In C_Parents.DefaultIfEmpty()" that I was leaving out.