How is this Nested Set SQL query converted into a LINQ query?

Querying a Nested Set Model table, here's the SQL... how can this be writt开发者_运维百科en 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;

particularly, the FROM part... never tried to do anything like that in LINQ.

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;

if you do the LEFT OUTER join like that (that is without using into and DefaultIfEmpty()) then the top-level parent node will not be output (because it's parent is NULL)

My SQL version which 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';

Translates 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 that helps, Paul.