Hey everyone,

Following up with the thread on SQL Trees and Nested Sets I thought I could use the code provided by MySQL site here and convert it to C#.

I'm hoping I could use LINQ to query the Tree Data Structure. I tried to do it by myself, but miserably failed lol. Hence I thought I'd ask for a quick conversion of one of the functions so I could use it a base to convert the others myself.

Code to convert: 
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;
I'd like to be able to do that in LINQ, but I still can't figure out a way to do a self join...

I tried this and this but for some reason it did not work out for me...
Ok I got this sorted out. I was just "not thinking right". Things are going fine now. Please close this thread.
And now I hit the wall... lol

I managed to write the following functions: 
 //Return all nodes that are leaves (have no children)
        public IQueryable<Category> FindLeaves()
        {
            IQueryable<Category> categs = null;
            try
            {
               categs = db.Categories.Where(c => c.Right == c.Left + 1);   
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return categs;
        }

        //Find the path to a certain node
        public IQueryable<Category> FindPath(string categoryName)
        {
            IQueryable<Category> categs = null;
            try
            {
                Category node = db.Categories.Where(c => c.Name == categoryName).FirstOrDefault();
                categs = db.Categories.Where(c => c.Left < node.Left && c.Right > node.Right).OrderBy(c => c.Left);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return categs;
        }

        //Find the depth of a certain node
        public int FindDepth(string categoryName)
        {
            int depth = 999999;
            try
            {
                Category node = db.Categories.Where(c => c.Name == categoryName).FirstOrDefault();
                depth = db.Categories.Where(c => c.Left < node.Left && c.Right > node.Right).Count();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return depth;
        }

        //Retrieve all the children and descendants of a certain node
        public IQueryable<Category> RetrieveDescendants(string parentName)
        {
            IQueryable<Category> categs = null;
            try
            {
                Category parent = db.Categories.Where(c => c.Name == parentName).FirstOrDefault();
                categs = db.Categories.Where(c => c.Left > parent.Left && c.Right < parent.Right).OrderBy(c => c.Left);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return categs;
        }
Now I need to retrieve the immediate children of a node, only those that are one level beneath the given node. Here's my attempt which does not work and does not throw any exceptions either... 
//Retreive only the immediate children of a certain node
        public IQueryable<Category> RetrieveImmediateChildren(string parentName)
        {
            IQueryable<Category> categs = null;
            try
            {
                Category parent = db.Categories.Where(c => c.Name == parentName).FirstOrDefault();
                var depth = FindDepth(parentName) + 1;
                //MessageBox.Show("Depth = " + depth);
                categs = db.Categories.Where(c => c.Left > parent.Left && c.Right < parent.Right).OrderBy(c => c.Left);
                categs = categs.Where(c => FindDepth(c.Name) == depth);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return categs;
        }
Any suggestions?
Kassem,

I haven't looked at the entire question, can you post the schema of categories please?

One more thing, instead of using :
db.Categories.Where(c => ... ).FirstOrDefault();
You could use:
db.Categories.FirstOrDefault(c => ... );
Alright...

The schema of Categories is: 
ID int PK
Name varchar(50)
Left int
Right int
On a side note, I am facing more issues now... I keep getting the error "Object reference not set to an instance of an object" when trying to add a new node using the following function: 
//Insert a new node after a certain node
        public bool AddNodeAfter(string nodeName, string newNodeName)
        {
            IQueryable<Category> categories = null;

            try
            {
                Category node = db.Categories.FirstOrDefault(c => c.Name == nodeName);
                categories = db.Categories.Where(c => c.Right > node.Right && c.Left > node.Right);
                if (categories != null)
                {
                    foreach (Category categ in categories) //when debugging I noticed that categories is what is throwing the exception
                    {
                        categ.Left += 2;
                        categ.Right += 2;
                    }

                    Category newNode = new Category
                    {
                        ID = 11,
                        Name = newNodeName,
                        Left = node.Right + 1,
                        Right = node.Right + 2
                    };

                    db.AddToCategories(newNode);

                    if (db.SaveChanges() > 0)
                        return true;
                }
                else
                {
                    MessageBox.Show("Categories variable is set to null", "Error!");
                }
            }
            catch (NullReferenceException e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
                return false;
            }

            return false;
        }
But I do not understand why this is happening. I already checked if "categories" is null, which is not the case. But this exception is still being thrown for some reason.

Any thoughts?
Kassem wroteBut I do not understand why this is happening. I already checked if "categories" is null, which is not the case. But this exception is still being thrown for some reason.

Any thoughts?
It's quite possible that the underlying expression has not been evaluated yet due to lazy loading.

Try this:
foreach(Category categ in categories.ToList()){
...
}
Unfortunately, that did not solve the problem either... Check this out:

I'm not sure this is of any help but thought I'd share it, maybe there's something I missed out on...
In case someone is interested, I've got the RetrieveImmediateChildren() function to work. Apparently there is an issue with calling a function inside the Lambda Extensions. Here's the fixed code:
        //Retreive only the immediate children of a certain node
        public IList<Category> RetrieveImmediateChildren(string parentName)
        {
            IQueryable<Category> categs = null;
            IList<Category> list = new List<Category>();
            try
            {
                //retrieve the node (parent)
                Category parent = db.Categories.FirstOrDefault(c => c.Name == parentName);
                //find the depth right below the parent
                var depth = FindDepth(parentName) + 1;
                //get ALL children of the retreived node
                categs = db.Categories.Where(c => c.Left > parent.Left && c.Right < parent.Right).OrderBy(c => c.Left);
                //check each child node's depth and add it to the list if it has the required depth
                foreach (Category c in categs.ToList())
                {
                    int d = FindDepth(c.Name);
                    if (d == depth)
                        list.Add(c);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.InnerException, "Error!");
            }
            return list;
        }
P.S: I know there's a way to call a "func" from Lambda Expressions, is that what I should be using here rather than having to do the foreach loop?