Linq select 10 children for each parent item

Question

I am using a nested set model and want to be able to select 10 parent items, as well as 10 child items for each parent, which are ordered.

I am trying to write a nested comment system, whereby a reply to a comment will be signified by having left and right values within its 'parent' comment.

I am having a real headache reducing the load times of large lists of items being retrieved and believe if I could do the above in a single Linq statement then I might be able to save a lot more time than making repeated calls to the db to get 10 children for each parent.

I use a this statement to get the parent(or root items) from the datacontext.

var query = context.Items
                   .Where(x => !context.Items.Any(y => y.LeftPos < x.LeftPos
                                                    && y.RightPos > x.RightPos))
                   .OrderBy(x => x.Id)
                   .Take(pageSize)
                   .ToList();

The above code is getting the outermost (parent) items in the nested set model by checking there are not any other items with left and right values outside of theirs.

So, rather than looping through the parentItems with a foreach (which I am doing currently) and making 10 or (pageSize) calls to the db on each iteration, how can I take 10 children of each parent node with a Linq statement?

EDIT:

I am using the nested set model. My items have left and right positions (leftPos and rightPos). So a child is an object with left and right values within the left and right values of another object, which in turn would be the parent.

1 a 4
  2 b 3

so if I have a lot of items

1 a 4
 2 b 3
5 c 10
 6 d 7
 8 e 9
11 f 14
 12 g 13

....

Is there a way I can select x amount of children from each parent using Linq?

Any help appreciated

Answer 1

    class Program
    {
        static void Main(string[] args)
        {
            List<A> lst = new List<A>();

            for (int j = 1; j < 4; j++)
            {
                var tmp = new A() { Value = j * 1000 };
                for (int i = 0; i < 150; i++)
                {
                    tmp.SubItems.Add(new B { Value = i + 1, Parent = tmp });
                }
                lst.Add(tmp);
            }

            List<B> result = lst.SelectMany(x => x.SubItems.Take(10)).ToList();
        }
    }

    public class A
    {
        public A()
        {
            SubItems = new List<B>();
        }

        public int Value { get; set; }
        public List<B> SubItems { get; set; }
    }


    public class B
    {
        public int Value { get; set; }
        public A Parent { get; set; }
    }

not sure if this is what you want. this way you get a collection of subitems. 10 subitems of each parent. you can access the parents with the .Parent property of each subitem...