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

Question

Querying a Nested Set Model table, here's the 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;

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

Answer 1

Perhaps something like:

var query = from node in nested_category
            from parentNode in nested_category
            where node.lft >= parentNode.lft and node.rgt <= parentNode.rgt
                and node.name ='FLASH'
            order by parent.left
            select parent.name;