How to Order entities with Parent/Child relationship together in a list

Question

I need to be able to order a list of Accounts in a particular order. They all have a one level parent/child relationship.

So, the data would look something like this:

AccountID    AccountName    ParentID
1            Blue           NULL
2            Red            NULL
3            Green          NULL
4            Yellow         3
5            Orange         2
6            Purple         1
7            Voilet         1
8            Gold           2

etc...

I need to populate a drop-down list that looks like the following (below) that is ordered by the AccountID with a NULL ParentID first alphbetically and then any child accounts for that Parent, also alphabetically. the "dash" on the child account is just added for visual effect so don't worry about that.

Blue
- Purple
- Voilet
Green
- Yellow
Red
- Gold
- Orange

Here's the code I was using previously (below) but it starts giving me this error after there around 30 or so accounts.

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

Public Function GetAllActiveAccountsForAccountSwitcher() As IEnumerable(Of Models.AccountDropDownListModel)
    Dim isFirst As Boolean = True
    Dim list As IQueryable(Of Models.AccountDropDownListModel) = Nothing

    Dim parentAccts As IQueryable(Of Account) = From a As Account In dc.Accounts _
            And a.ParentID Is Nothing _
            Order By a.AccountName

    For Each parentAcct In parentAccts

        Dim parent = From a In dc.Accounts Where a.AccountID = parentAcct.AccountID _
            Select New Models.AccountDropDownListModel _
            With { _
                .AccountID = a.AccountID,
                .AccountName = a.AccountName
            }
        If isFirst Then
            list = parent
            isFirst = False
        Else
            list = list.Union(parent)
        End If

        Dim child = From a As Account In dc.Accounts Where a.ParentID = parentAcct.AccountID _
           Select New Models.AccountDropDownListModel _
                With { _
                    .AccountID = a.AccountID,
                    .AccountName = "- " & a.AccountName
                }
        list = list.Union(child)

    Next

    Return list
End Function

C# or VB.NET examples are fine. I'm agnostic but it needs to use linq-to-sql. Stored Procs are not an options for my situation.

UPDATE: here is c# of my original code for anyone that is allergic to VB...

public IEnumerable<Models.AccountDropDownListModel> GetAllActiveAccountsForAccountSwitcher()
{
    bool isFirst = true;
    IQueryable<Models.AccountDropDownListModel> list;

    IQueryable<Account> parentAccts = from a in dc.Accounts & a.ParentID == null orderby a.AccountName;


    foreach (void parentAcct_loopVariable in parentAccts) {
        parentAcct = parentAcct_loopVariable;
        var parent = from a in dc.Accountswhere a.AccountID == parentAcct.AccountID select new Models.AccountDropDownListModel {
            AccountID = a.AccountID,
            AccountName = a.AccountName
        };
        if (isFirst) {
            list = parent;
            isFirst = false;
        } else {
            list = list.Union(parent);
        }

        var child = from a in dc.Accountswhere a.ParentID == parentAcct.AccountID select new Models.AccountDropDownListModel {
            AccountID = a.AccountID,
            AccountName = "- " + a.AccountName
        };
        list = list.Union(child);

    }

    return list;
}

Answer 1

Re-wrote it. We can't keep a proper sort order maintained by a trigger like we should, so you have to do a little trickery. I'm not sure if this will work directly i Linq2Sql, but since you need all the records you can just pull them all into a List<Account> and then sort that list on the client side.

The things we will order by, in the following order:

1. The name of the parent OR or our own name if null (groups all items from one parent together)
2. The fact that a row is a parent (puts the parent on top)
3. The name (orders the children, not affecting the parent)

Here is a query that works for me on a list of objects that match your schema:

var result = 
    from a in Accounts
    // Get one value that is the same for both a parent & a child.
    // Since we need to sort alphabetically, we use the
    // parent name.  We only have the ID (parentId), so we need to do
    // a lookup into the list to get the parent.Name.  If the parentId
    // is null, it means we ARE the parent, so we use our own name
    let sortingGroup = Accounts.Where(x => x.Id == a.ParentId)
                            .Select(x => x.Name)
                            .FirstOrDefault() ?? a.Name
    orderby
        sortingGroup,
        a.ParentId == null descending,
        a.Name
    select new
        {
            SortingGroup = sortingGroup,
            Id = a.Id,
            Name = a.Name,
            ParentId = a.ParentId
        };
    }

Answer 2

RRrrrggg I'm so dumb. This turned out to be really simple. I just added a "SortName" string to my model and then sorted by it afterwards.

Public Function GetAllActiveAccountsForAccountSwitcher() As IEnumerable(Of Models.AccountDropDownListModel)
    Dim parentAccts As IQueryable(Of Account) = From a As Account In dc.Accounts

    Return parentAccts.Select(Function(a) New Models.AccountDropDownListModel _
                With { _
                    .SortName = IIf(a.ParentID Is Nothing, a.AccountID, a.ParentID & "_Child"),
                    .AccountID = a.AccountID,
                    .AccountName = IIf(a.ParentID Is Nothing, a.AccountName, "- " & a.AccountName)
                }).OrderBy(Function(a) a.SortName).ThenBy(Function(a) a.AccountName)
End Function