c# - Linq To实体，从选择中选择

Question

我已经尝试了一段时间，但我无法在 LINQ to Entites 中进行此选择？我怎样才能在选择上进行选择？

SELECT * FROM tProduct AS tpp, tProductGroupDetail AS tppg,
    (SELECT tpgd.ProductGroupID AS pgID 
     FROM tProduct AS tp, tProductGroupDetail AS tpgd
     WHERE tp.ProductID = 428 AND tpgd.ProductID = tp.ProductID) AS JoinedTable
WHERE JoinedTable.pgID = tppg.ProductGroupID
      AND tpp.ProductID = tppg.ProductGroupID

Answer 1

你的SQL语句很奇怪。是不是等同于下面的语句？

SELECT *
FROM
    tProduct AS p
    INNER JOIN tProductGroupDetail AS pgd
        ON p.ProductID = pgd.ProductGroupID
WHERE
    pgd.ProductID = 428

相应的 LINQ 查询将非常明显。

---

让我们逐步更改您的 SQL:

嵌套的 SELECT 看起来像这样:

(SELECT tpgd.ProductGroupID AS pgID 
FROM tProduct AS tp, tProductGroupDetail AS tpgd
WHERE tp.ProductID = 428 AND tpgd.ProductID = tp.ProductID)

这是一样的

(SELECT tpgd.ProductGroupID AS pgID 
FROM tProductGroupDetail AS tpgd
WHERE tpgd.ProductID = 428)

由于 tpgd.ProductID = tp.ProductID，您可以直接直接测试 tpgd.ProductID = 428，而无需加入 tp。简单语句现在看起来像这样:

SELECT * FROM tProduct AS tpp, tProductGroupDetail AS tppg,
    (SELECT ProductGroupID
     FROM tProductGroupDetail
     WHERE ProductID = 428) AS JoinedTable
WHERE JoinedTable.ProductGroupID = tppg.ProductGroupID
      AND tpp.ProductID = tppg.ProductGroupID

基本上，它的作用是将 tProduct 与 tProductGroupDetail 连接起来。使用 JoinedTable 进行额外联接的目的是限制记录仅包含具有 ProductID = 428 的产品组。这个条件可以直接应用:

SELECT *
FROM tProduct AS tpp, tProductGroupDetail AS tppg,
WHERE tppg.ProductID = 428 -- Note: ProductID of tggp, NOT tpp!
     AND tpp.ProductID = tppg.ProductGroupID

最后一步是用 INNER JOIN 子句替换连接并调整别名。这产生了我的第一个 SQL 语句。