Parent Child Relationship Linq to SQL

Question

I am using Linq to SQL and have tables like

*ItemTable*
ItemId Description 
1      Root 
2      Child 1
3      Child 2
4      Child of Child1
5      Child of Child1

*ItemChildTable* 
ParentID ChildId 
1          2
1          3
2          4
3          5

What will be best way to model them using LINQ to SQL So that I can get a object relations like

Item parent;
Item child = parent.ChildItems.First();

Here I want the childItems to be of same type as Items and relationship of parent and child stored in another table

Answer 1

Your trying to get recursion in your LINQ query. I don´t know if its possible or if it would be efficient. It think you should leave this one up to C#. Like so:

var items = db.ItemTables.ToList();
var itemRelations = db.ItemChildTables.ToList();

var root = items.SingleOrDefault(item => ! itemRelations.Exists(rel => rel.ChildId == item.ItemId));
if(root != null)
{
    Item rootItem = new Item();
    rootItem.ItemId = root.ItemId;
    List<Item> childLessItems = new List<Item>{ rootItem };
    while(childLessItems.Count > 0)
    {
        List<Item> newChildLessItems = new List<Item>();
        foreach(var parent in childLessItems)
        {
            var childIds =  from rel in itemRelations
                            where rel.ParentID == parent.ItemId
                            select rel.ChildId;
            var children = items.Where(maybeChild => childIds.Contains(maybeChild.ItemId));
            foreach(var child in children)
            {
                Item childItem = new Item();
                childItem.ItemId = child.ItemId;
                childItem.Parents.Add(parent);
                    parent.ChildItems.Add(childItem);
                newChildLessItems.Add(child);
            }
        }
        childLessItems = newChildLessItems;
    }
}