Performing a Parent then Child sort in Linq

Question

The intention is to sort the list by the parent, and then the child (there will only be one child).

Example Set:
ID  ParentId Type   Unit
1   NULL    Energy  kJ
2   1       Cal
3   NULL    Protein g
4   NULL    Fat, total  g
5   4       Saturated   g
6   NULL    Carbohydrate    g
7   6       Sugars  g
8   NULL    Dietary fibre   g
10  NULL    Sodium  mg
11  NULL    Potassium   mg

So for example, if I sort by Type (alphabetical order) it would come up

Carbohydrate
Sugars (parent = 1.)
Dietary fibre
Energy
Cal (parent = 4.)
Fat, total
Saturated (parent = 6.)

when you say by parent and then by child, do you mean the IDs? Or do you mean you want to see P1, C1, P2, C2, P3, C3, etc...? It's a good question, it's just unclear exactly what results you'd like to see. Perhaps a sample showing the results you'd expect? — James Michael Hare, Oct 19 '12 at 14:15
I would assume from your data you are guaranteed that a child id is never greater than its parent id? — James Michael Hare, Oct 19 '12 at 14:20
@JamesMichaelHare Yes, currently, but it may not always be this way. — ediblecode, Oct 19 '12 at 14:20
@iMortalitySX i don't think it's that simple. The OP in effect wanting to join the list on itself — James Michael Hare, Oct 19 '12 at 14:20
[Multiple Order By LINQ](http://stackoverflow.com/questions/298725/multiple-order-by-in-linq) — iMortalitySX, Oct 19 '12 at 14:23
[Self Join Query LINQ](http://stackoverflow.com/questions/5026970/linq-self-join-query-how-to-accomplish-this) — iMortalitySX, Oct 19 '12 at 14:25
@JamesMichaelHare Indeed, you are probably correct. I am just adding links to similar issues because I believed that is what danrhul was looking for. danrhul, can you add what you expect the query to look like, maybe even in a SQL context? That would help. — iMortalitySX, Oct 19 '12 at 14:38

Bobson · Accepted Answer · 2012-10-19T15:25:17.483

6

Try this:

return myData.Select(x => new { key = (x.Parent ?? x).Type, item = x})
             .OrderBy(x => x.key)
             .ThenBy(x => x.item.Parent != null)
             .Select(x => x.item);

edited Oct 19 '12 at 15:25

answered Oct 19 '12 at 14:32

Bobson

12,858
4
49
78

This works. Just for future users: it should be `.ThenBy(x => x.item.Parent == null)` – ediblecode Oct 19 '12 at 14:40
Oops. Edited. Thanks for catching that. – Bobson Oct 19 '12 at 14:41
1

Ah yeah, this actually doesn't work, it just seemed to because my data was already in the right order. – ediblecode Oct 19 '12 at 14:47
Orders by the Id column it seems. I added another element to the table you see above, who's parentId was 1. But it came up last. – ediblecode Oct 19 '12 at 14:49
Hmm. Try putting a .ToList() after the .ThenBy()? Force it to evaluate the sort before altering what we're selecting. – Bobson Oct 19 '12 at 14:50
This ordered to all children and then all parents – ediblecode Oct 19 '12 at 14:53
@danrhul - I just got a chance to actually try it. I just needed to invert the ThenBy(). Try the current version. – Bobson Oct 19 '12 at 15:26

Sergey Berezovskiy · Answer 2 · 2012-10-19T15:38:36.747

This could be done in two steps. First - building parent-child hierarchy (and sorting it):

var query = from parent in data
            where parent.ParentId == null
            orderby parent.Type
            join child in data on parent.ID equals child.ParentId into g
            select new { Parent = parent, Children = g };

Second - flattering hierarchy

var result = query.Flatten(x => x.Parent, x => x.Children);

For flattering I used extension method:

public static IEnumerable<TResult> Flatten<T, TResult>(
    this IEnumerable<T> sequence, 
    Func<T, TResult> parentSelector,
    Func<T, IEnumerable<TResult>> childrenSelector)
{
    foreach (var element in sequence)
    {
        yield return parentSelector(element);

        foreach (var child in childrenSelector(element))
            yield return child;
    }
}

Aducci · Answer 3 · 2012-10-19T14:51:52.193

0

This should work

var query = from p in context.Table
            from c in context.Table.Where(x => p.ID == x.ParentId)
                                   .DefaultIfEmpty()
            let hasNoParent = c == null
            orderby hasNoParent ? p.Type : c.Type, hasNoParent ? 0 : 1
            select hasNoParent ? p.Type : c.Type;

edited Oct 19 '12 at 14:51

answered Oct 19 '12 at 14:42

Aducci

24,033
7
60
64

Apparently `c` does not exist in the current context – ediblecode Oct 19 '12 at 14:50
@danrhul - Sorry, it should have been x.ParentId – Aducci Oct 19 '12 at 14:52
Didn't work I'm afraid, 6 nulls, followed by random repeating data – ediblecode Oct 19 '12 at 15:00
I had but hadn't noticed the change of `p : c` Even still, it just duplicates a couple of records and then just displays them in alphabetical order. – ediblecode Oct 19 '12 at 15:06

Performing a Parent then Child sort in Linq

3 Answers3

Linked