如何在列表中一起订购具有父子关系的实体 [英] How to Order entities with Parent/Child relationship together in a list

问题描述

我需要能够以特定顺序订购帐户列表.他们都有一级父母/子女关系.

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

我需要填充一个如下所示的下拉列表(如下所示)，该列表由AccountID进行排序，并先按字母顺序将NULL ParentID设置为NULL，然后再按字母顺序对该父级的所有子帐户进行排序.子帐户上的破折号"只是为了视觉效果而添加的，因此不必担心.

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

这是我以前(下面)使用的代码，但是在大约30个左右的帐户之后，它开始给我这个错误.

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

您的SQL语句的某些部分嵌套得太深.重写查询或将其分解为较小的查询.

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#或VB.NET示例很好.我不可知，但它需要使用linq-to-sql.存储的Procs不适用于我的情况.

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.

更新:这是我原始代码的c#，适用于对VB过敏的任何人...

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;
}

推荐答案

重新编写.我们不能像我们应该的那样通过触发器保持适当的排序顺序，因此您必须做些小技巧.我不确定Linq2Sql是否可以直接使用它，但是由于您需要所有记录，因此可以将它们全部放入List<Account>中，然后在客户端对该列表进行排序.

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. 父母的名字或我们自己的名字，如果null(将来自一个父母的所有项目组合在一起)
2. 行是父级的事实(将父级放在顶部)
3. 名字(排序孩子，不影响父母)

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
        };
    }

这篇关于如何在列表中一起订购具有父子关系的实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！