Databases
StackOverflow2013
Postsdbo
POHow to Order entities with Parent/Child relationship together in a list
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Order entities with Parent/Child relationship together in a list
    primarykey
    data
    text
    <p>I need to be able to order a list of Accounts in a particular order. They all have a one level parent/child relationship.</p> <p>So, the data would look something like this:</p> <pre><code>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... </code></pre> <p>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.</p> <pre><code>Blue - Purple - Voilet Green - Yellow Red - Gold - Orange </code></pre> <p>Here's the code I was using previously (below) but it starts giving me this error after there around 30 or so accounts.</p> <p><em><strong>Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.</em></strong></p> <pre><code>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 = "- " &amp; a.AccountName } list = list.Union(child) Next Return list End Function </code></pre> <p>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.</p> <p><strong>UPDATE:</strong> here is c# of my original code for anyone that is allergic to VB...</p> <pre><code>public IEnumerable&lt;Models.AccountDropDownListModel&gt; GetAllActiveAccountsForAccountSwitcher() { bool isFirst = true; IQueryable&lt;Models.AccountDropDownListModel&gt; list; IQueryable&lt;Account&gt; parentAccts = from a in dc.Accounts &amp; 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; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRichC
    1. USRichC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload