How does this nested set SQL query translate to a LINQ query?

Question

The query is a table of nested model sets , here is SQL ... how can this be written in LINQ?

SELECT parent.name
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'FLASH'
ORDER BY parent.lft;

in particular, the FROM part ... never tried to do anything similar in LINQ.

Answer 1

Perhaps something like:

var query = from node in nested_category
            from parentNode in nested_category
            where node.lft >= parentNode.lft && node.rgt <= parentNode.rgt
                 && node.name == "FLASH"
            orderby parent.left
            select parent.name;

Answer 2

if you enter LEFT OUTER (without using in and DefaultIfEmpty () ), then the parent> top level will not be displayed (since it is a NULL parent)

My SQL version that goes:

SELECT 
    B.ELEMENT_CODE ParentElement,
    A.ELEMENT_CODE ChildElement
FROM NESTED_SET_STRUCTURE AS A
LEFT OUTER JOIN NESTED_SET_STRUCTURE AS B
  ON B.PROCEDURE = 'TEST1' AND B.FROM_LEFT = (SELECT MAX(C.FROM_LEFT)
             FROM NESTED_SET_STRUCTURE AS C
             WHERE C.PROCEDURE = 'TEST1' AND A.FROM_LEFT > C.FROM_LEFT
               AND A.FROM_LEFT < C.TO_RIGHT)
WHERE
    A.PROCEDURE = 'TEST1';

Translated as:

var nestedSets1 = 
    from a in NESTED_SET_STRUCTUREs
    join b in NESTED_SET_STRUCTUREs on 
    new { a.PROCEDURE, FROM_LEFT = ((from c in NESTED_SET_STRUCTUREs 
        where c.PROCEDURE.Equals("TEST1") 
        && a.FROM_LEFT > c.FROM_LEFT 
        && a.FROM_LEFT < c.TO_RIGHT
        select (c.FROM_LEFT)).Max()) } equals 
    new { b.PROCEDURE, b.FROM_LEFT } into bo
    from bb in bo.DefaultIfEmpty()
    where
        a.PROCEDURE.Equals("TEST1") 
    orderby a.FROM_LEFT
    select new {
        ParentElement = bb.ELEMENT_CODE,
        ChildElement = a.ELEMENT_CODE
    };

Hope this helps, Paul.