Convert SQL with Outer Apply into LINQ-CodePudding

Please help me convert this into LINQ. I am having trouble with the OUTER APPLY(that is meant to check if the record has children down the hierarchy).

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];

CodePudding user response：

This was a great reference: https://stackoverflow.com/a/64945881/10646316

THanks 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
                              };

CodePudding user response：

Following my SQL Recipe, updated for APPLY, I would translate the SQL (more or less) literally as:

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
          };

But I would prefer to move the where clauses closer to their corresponding tables, so then I would have:

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
          };

NOTE: I prefer translating a BETWEEN b AND c as b <= a && a <= c which I think makes the meaning clearer.