How to order by 2 fields with relation child/parent. Sort it so that child items follow their parents in the sorted list?

Question

I am query in table as below.

Id
BeforeId
Description

For Example:

Id        BeforeId       Description
1         NULL           test
2         NULL           test1
3         2              test2
4         3              test3

If BeforeId is not null will push before Id. I would like to order by Id and BeforeId with sort in the following order.

Id        BeforeId       Description
1         NULL           test
4         3              test3
3         2              test2
2         NULL           test1

I am trying code as below but it is not true.

var listOrder = _entites.Orders.OrderBy(t => t, new CustomComparer()).ToList();

 public class CustomComparer : IComparer<Order>
{
    public int Compare(Order lotA, Order lotB)
    {
        if (lotA.BeforeId!=null)
        {
            if (lotB.Id == lotA.BeforeId)
            {
                return -1;
            }
            else
            {
                return 0;
            }
        }
        else if(lotB.BeforeId != null )
        {
            if(lotA.Id == lotB.BeforeId)
            {
                return 1; // A > B
            }
            else
            {
                return 0;
            }

        }
        else
        {
            return 0;
        }
    }
}

Can anyone tell me how to do solve this problem.

Thank you!

Just so I understand your question: You're trying to sort it so that child items follow their parents in the sorted list, where the relationship is defined by BeforeId/Id? — Llama, Oct 07 '19 at 02:32
i want to order with complex function in two field. But i don't how to do? — binh, Oct 07 '19 at 02:32
Note that `Compare` is expected to return -1, 0, or 1 because the result should indicate where `lotA` should be located relative to `lotB` (before -1, same place 0, after 1). — Llama, Oct 07 '19 at 02:33
Possible duplicate of [Multiple "order by" in LINQ](https://stackoverflow.com/questions/298725/multiple-order-by-in-linq) — David, Oct 07 '19 at 02:42
@John: That right. I think iscompare is not suitable with my situation because it return value when find before id and not sorting again!. Do you have idea for this one! — binh, Oct 07 '19 at 02:49
I suggest to view your problem not as a simple sorting issue. It might help to look at your data as creating a graph of connected nodes. Be aware that this graph might contain cycles, so that a simple ordering is impossible. Example: A claims to be before B, B claims to be before C, and C claims to be before A. What now? Depending on where your data comes from, it might be necessary to first verify that you in fact have an "directed acyclic graph" (see https://en.wikipedia.org/wiki/Directed_acyclic_graph), before finding the topological order of that graph. — pniederh, Oct 08 '19 at 07:20
@pniederh: I find the solution by generate sequence number for each row. After that order by sequence number. — binh, Oct 11 '19 at 01:35
@binh Ok, sounds reasonable. Maybe you want to post your solution here as an answer, for the benefit of others with a similar problem. — pniederh, Oct 11 '19 at 10:52

binh · Accepted Answer · 2019-10-16T01:39:25.903

The first one I create a view model(add Seq column) and sort it like this

Id        BeforeId       Description   Seq
1         NULL           test           1
2         NULL           test1          2
3         2              test2          3
4         3              test3          4

I will auto generate sequence number. After that i will update seq by find each element have before id to sort list again. It may take a lot of time with big data.

//list order need to sort
var listNeedToSort = _entites.Order.ToList();
//list order have before id
var listBeforeId = listNeedToSort.Where(p=>p.BeforeId!=null).Select(p => p.BeforeId).ToList();
 //count number of duplicate data is not process
 int countLoopDuplicateButNotProcess = 0;

  while (listBeforeId.Any())
                {
                    foreach (var item in listNeedToSort.OrderByDescending(p => p.BeforeId))
                    {
                        if (item.BeforeId != null)
                        {
                            //get record which is mentioned by other record through beforeid.
                            var recordSummary = listNeedToSort.FirstOrDefault(p => p.Id == item.BeforeId);

                            if (recordSummary != null)
                            {
                                // if sequence number of item with before id greater than record which has id equals beforeid
                                if (item.Seq > recordSummary.Seq)
                                {
                                    //reset count loop but it process again
                                    countLoopDuplicateButNotProcess = 0;
                                    item.Seq = recordSummary.Seq;
                                    //sort again list
                                    foreach (var item1 in listNeedToSort.Where(p => p.Seq >= recordSummary.Seq && p.Id != item.Id).OrderBy(p => p.Seq))
                                    {
                                        item1.Seq += 1;
                                    }
                                    //remove beforeid in listBeforeId
                                    listBeforeId.Remove(item.BeforeId);
                                }
                                else
                                {
                                    //not process
                                    countLoopDuplicateButNotProcess += 1;
                                }
                            }
                            else
                            {
                                //reset count loop but it process again
                                countLoopDuplicateButNotProcess = 0;
                                  //remove beforeid in listBeforeId
                                listBeforeId.Remove(item.BeforeId);
                            }
                        }
                        else
                        {
                            //not process
                            countLoopDuplicateButNotProcess += 1;
                        }
                    }
                    //break if not process two times.
                    if (countLoopDuplicateButNotProcess == 2)
                    {
                        break;
                    }
                }

How to order by 2 fields with relation child/parent. Sort it so that child items follow their parents in the sorted list?

1 Answers1