LINQ2SQL: orderby note.hasChildren(), name ascending

Question

I have a hierarchical data structure which I'm displaying in a webpage as a treeview.

I want to data to be ordered to first show nodes ordered alphabetically which have no children, then under these nodes ordered alphabetically which have children. Currently I'm ordering all nodes in one group, which means nodes with children appear next to nodes with no children.

I'm using a recursive method to build up the treeview, which has this LINQ code at it's heart:

    var filteredCategory = from c in category
                           orderby c.Name ascending
                           where c.ParentCategoryId == parentCategoryId && c.Active == true
                           select c;

So this is the orderby statement I want to enhance.

Shown below is the database table structure:

[dbo].[Category](
    [CategoryId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Level] [tinyint] NOT NULL,
    [ParentCategoryId] [int] NOT NULL,
    [Selectable] [bit] NOT NULL CONSTRAINT [DF_Category_Selectable]  DEFAULT ((1)),
    [Active] [bit] NOT NULL CONSTRAINT [DF_Category_Active]  DEFAULT ((1))

Answer 1

Try this (replace c.Category) with the child IQueryable<>:

var filteredCategory = from c in category 
       orderby c.Category.Count == 0
       where c.ParentCategoryId == parentCategoryId && c.Active == true 
       select c; 

Answer 2

If you're using Linq2SQL, can't you build the tree by just getting all the Categories without parent, and then access the children as needed to build the tree?

I mean something like this query to select the root Categories:

Category[] rootCats = (from c in category
    orderby c.Name ascending
    where c.ParentCategoryId == 0 // that is, no parent = root element
        && c.Active == true
    select c).ToArray();

And then accessing the children of a given Category (IEnumerable<Category>) via oneCat.Category.Where( cat => cat.Active == true).

Answer 3

var filteredCategory = category
  .Where(c => c.ParentCategoryId == parentCategoryId
    && c.Active == true)
  .OrderBy(c => c.Children.Any() ? 1 : 2)
  .ThenBy(c => c.Name);

If you don't have a Children property, go to the linq to sql designer and create an association that adds the property.