Subsonic 3 and The Adjacency Model

Question

If a table has the adjacency model applied (ID,ParentID) how can the heirarchy be returned in Subsonic 3?

Answer 1

I use this all the time. Just read all the rows. It's what you do with the rows later that matters. I feed them into a DevExpress tree control that automatically picks apart the ID and ParentID to show the tree. Or with a few lines of recursion you can traverse the tree and do anything you like.

var result = from p in _db.Products select p;

Answer 2

All classes are partials, so create a new partial for your class (let's say it' Category) and create child collection (call it SubCategories). Then when you load your object into memory you can load the subcollection:

var allCategories=Categories.All().ToList();
allCategories.ForEach(x=>x.SubCategories=allCategories.Where(y=>y.CategoryID==x.ParentID));

That's freehanded, but that's the idea.

Answer 3

This is what I have thanks to Rob but I wonder whether the recursive function is still needed? Is there a LINQ alternative?

        var allCategories = Table_1.All().ToList();
        allCategories.ForEach(x => x.Children = allCategories.Where(y => y.ParentID == x.ID));

        var topLevel = allCategories.Where(f => f.ParentID == 0);

        s.AppendLine("<ul>");
        DoStuff(topLevel);
        s.AppendLine("</ul>");

        private void DoStuff(IEnumerable<Table_1> toplevel)
        {
           foreach (var lve in toplevel)
           {
            s.AppendLine("<li>"+lve.Title);
            if (lve.Children.Count() > 0)
            {
                s.AppendLine("<ul>");
                DoStuff(lve.Children);
                s.AppendLine("</ul>");
            }
            s.AppendLine("</li>");
            }
        }