將帶有外部應用的SQL轉換為LINQ

請幫我將其轉換為 LINQ。我在使用 OUTER APPLY 時遇到了問題（這是為了檢查記錄是否在層次結構中有子級）。

SELECT phNode.Id,
                            phNode.[Description],
                            phNode.CreatedAt,
                            phNode.[Left],
                            phNode.[Right],
                            phNode.LastUpdated,
                            coalesce(c.HasChildren, 0) HasChildren,
                            phNode.LevelTypeId
                      FROM ProductHierarchy phNode                ,
                    ProductHierarchy AS parent
                      OUTER APPLY
                     (
                       select top 1
                              1 as HasChildren
                         from ProductHierarchy ph2
                        where ph2.[Left] > parent.[Left] and
                        ph2.[Right] < parent.[Right]
                     ) c

                     -- Get first child record. Returns null record if not found.
                   
                     WHERE phNode.[left] BETWEEN parent.[left] AND parent.[Right]
                    AND   parent.Id = 6
                    AND phNode.LevelTypeId  = 4
                    ORDER BY phNode.[left];

uj5u.com熱心網友回復：

這是一個很好的參考：https : //stackoverflow.com/a/64945881/10646316

感謝https://stackoverflow.com/users/10646316/svyatoslav-danyliv

from phNode in _context.ProductHierarchy
                              from parent in _context.ProductHierarchy
                              where phNode.Left > parent.Left && phNode.Right < parent.Right
                                    && parent.Id == parentId
                                    && phNode.LevelTypeId == childrenLevelId
                              from t2Data in _context.ProductHierarchy
                                  .Where(t2 => t2.Left > parent.Left &&
                                          t2.Right < parent.Right)
                                  .Select(x => true).Take(1).DefaultIfEmpty()
                              select new ProductHierarchyWithHasChildren()
                              {
                                  Id = phNode.Id,
                                  Description = phNode.Description,
                                  CreatedAt = phNode.CreatedAt,
                                  LastUpdated = phNode.LastUpdated,
                                  HasChildren = t2Data,
                                  Left = phNode.Left,
                                  Right = phNode.Right,
                                  LevelTypeId = phNode.LevelTypeId
                              };

uj5u.com熱心網友回復：

按照我的 SQL Recipe，更新為APPLY，我會將 SQL（或多或少）從字面上翻譯為：

var ans = from phNode in ProductHierarchy
          from parent in ProductHierarchy
          from c in (from ph2 in ProductHierarchy
                     where ph2.Left < parent.Left && ph2.Right < parent.Right
                     select new { HasChildren = true })
                    .Take(1).DefaultIfEmpty()
          where parent.Left <= phNode.Left && phNode.Left <= parent.Right &&
                parent.Id == 6 && phNode.LevelTypeId == 4
          orderby phNode.Left
          select new {
              phNode.Id,
              phNode.Description,
              phNode.CreatedAt,
              phNode.Left,
              phNode.Right,
              phNode.LastUpdated,
              HasChildren = c.HasChildren,
              phNode.LevelTypeId
          };

但我更愿意將where子句移近它們對應的表，所以我會有：

var ans = from phNode in ProductHierarchy
          where phNode.LevelTypeId == 4
          from parent in ProductHierarchy
          where parent.Left <= phNode.Left && phNode.Left <= parent.Right &&
                parent.Id == 6
          from c in (from ph2 in ProductHierarchy
                     where ph2.Left < parent.Left && ph2.Right < parent.Right
                     select new { HasChildren = true })
                    .Take(1).DefaultIfEmpty()
          orderby phNode.Left
          select new {
              phNode.Id,
              phNode.Description,
              phNode.CreatedAt,
              phNode.Left,
              phNode.Right,
              phNode.LastUpdated,
              HasChildren = c.HasChildren,
              phNode.LevelTypeId
          };

注意：我更喜歡翻譯a BETWEEN b AND c，b <= a && a <= c因為我認為這會使意思更清楚。

標籤：sql 林克

上一篇：使用LINQ查詢檢查特定值是否在表中出現多次

下一篇：linq過濾列而不是資料