LINQ2SQL: orderby note.hasChildren(), name ascending

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 un开发者_高级运维der 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))

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.

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; 

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).