SQL查询转换为LINQ左外连接（VB.NET）

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.

我四处寻找,并花了很长时间尝试将此SQL语句转换为VB中的Linq语句。我相信这对其他人来说是一个很好的例子 - 该声明试图推出与产品类别具有多对多关系的产品,并且这些类别具有父母/子女的等级。

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.

我可以达到我试图说“哪里......(P_Cats.Parent = 9)”但我无法想出那部分的地步。

THANKS!

1 个解决方案

#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.

我离开的是“来自cParents在C_Parents.DefaultIfEmpty()”的这一行。

#1