Linq to get immediate children in Nested Set Model

Question

In Nested Set Model, there's a challenge for me to convert sql to linq.

The above wikipedia link shows how to list up immediate children of given node as below sql syntax, and it works well when I test it with LinqPad.

SELECT DISTINCT Child.Name
FROM ModelTable AS Child, ModelTable AS Parent 
WHERE Parent.Lft < Child.Lft AND Parent.Rgt > Child.Rgt  -- associate Child Nodes with ancestors
GROUP BY Child.Name
HAVING MAX(Parent.Lft) = 16  -- Subset for those with the given Parent Node as the nearest ancestor

I'm stuck with expressing it with LINQ, so I'm on my knees to learn from you.

Quianaquibble

Answer 1

This works (note the distinct is superfluous in the SQL):

from c in nodes
from p in nodes
where c.Left > p.Left && c.Right < p.Right
group p by c into g
where g.Max(x => x.Left) == 1
select g.Key;

Full sample for linqpad:

var nodes = new [] { new {Name = "Clothing", Left = 1, Right = 22} }.ToList();

nodes.Add(new {Name = "Clothing", Left = 1, Right = 22});
nodes.Add(new {Name = "Men's", Left = 2, Right = 9});
nodes.Add(new {Name = "Women's", Left = 10, Right = 21});
nodes.Add(new {Name = "Suits", Left = 3, Right = 8});
nodes.Add(new {Name = "Slacks", Left = 4, Right = 5});
nodes.Add(new {Name = "Jackets", Left = 6, Right = 7});
nodes.Add(new {Name = "Dresses", Left = 11, Right = 16});
nodes.Add(new {Name = "Skirts", Left = 17, Right = 18});
nodes.Add(new {Name = "Blouses", Left = 19, Right = 20});
nodes.Add(new {Name = "Evening Gowns", Left = 12, Right = 13});
nodes.Add(new {Name = "Sun Dresses", Left = 14, Right = 15});

var q =
    from c in nodes
    from p in nodes
    where c.Left > p.Left && c.Right < p.Right
    group p by c into g
    where g.Max(x => x.Left) == 1
    select g.Key;

q.Dump();

Distasteful