linq order by based on multiple columns and various crietria

Question

I have data arranged in this order in sql.

Now, I want to order this list with both QuestionDataTypeId and DisplayOrder, but want this QuestionDataTypeId = 0 at last. so finally result will be like first row = 6 then 7 then 8 and then 1 to 5.

I want to achieve this with C# and linq.

What I have tried so far?

here is my code but it's not working.

var data = entity
    .OrderByDescending(m => m.QuestionDataTypeId == 0)
    .ThenBy(m => m.QuestionDataTypeId)
    .ThenBy(m => m.DisplayOrder);

I have fixed this with merging 2 different variables sorted separately for QuestionDataTypeId = 0 and all other QuestionDataTypeId, but just want to know what will be the proper linq for this case in single line.

any help would be really appreciated. thanks!

Seems like a good solution to me. Similar approach suggested here: https://stackoverflow.com/questions/15023226/sql-sort-by-priority-but-put-0-last — Johnathan Barclay, Sep 08 '21 at 15:30
Ultimately your LINQ will be converted into SQL, unless you do your sorting client side which should be avoided if possible. — Johnathan Barclay, Sep 08 '21 at 15:39
agree, but the data stored in db is not in properly arranged. so when retrieving data, I've to sorting in this manner. — Bharat, Sep 08 '21 at 15:40
I think your first sort should be OrderBy instead of OrderByDescending. Try: `.OrderBy(m => m.QuestionDataTypeId == 0)` — user2051770, Sep 08 '21 at 16:59
Yeah, false < true, so just `OrderBy`. What's the meaning of "it's not working"? — Gert Arnold, Sep 08 '21 at 17:42

score 1 · Answer 1 · answered Sep 08 '21 at 16:27

You can write your own comparer for OrderBy

Sample data structure:

public record Table
{
    public Table(int qdtId, int displayOrder, string text)
    {
        QuestionDataTypeId = qdtId;
        DisplayOrder = displayOrder;
        Text = text;
    }

    public int QuestionDataTypeId {  get; set; }
    public int DisplayOrder {  get; set; }
    public string Text { get; set; }
}

public class TableComparer : IComparer<Table>
{
    public int Compare(Table? x, Table? y)
    {
        if(x.QuestionDataTypeId!= 0 && y.QuestionDataTypeId!=0 || x.QuestionDataTypeId == 0 && y.QuestionDataTypeId == 0)
        {
            return y.QuestionDataTypeId.CompareTo(x.QuestionDataTypeId);
        }
        return x.QuestionDataTypeId == 0 && y.QuestionDataTypeId != 0 ? int.MinValue : int.MaxValue;
    }
}

Then in the code:

List<StringConcatBug.Table> list = new()
{
    new(0, 1, "Comfortable"),
    new(0, 2,"attainable"),
    new(0, 3,"recent goal"),
    new(0, 4,"comfortable"),
    new(2, 2,"Last Name"),
    new(3, 3,"Email"),
    new(0, 5, "feeling"),
    new(1, 1, "First Name"),

};

var ordered = list.OrderByDescending(t=>t,new TableComparer());

foreach(var v in ordered) {  Console.WriteLine(v);}

Output

Table { QuestionDataTypeId = 1, DisplayOrder = 1, Text = First Name }
Table { QuestionDataTypeId = 2, DisplayOrder = 2, Text = Last Name }
Table { QuestionDataTypeId = 3, DisplayOrder = 3, Text = Email }
Table { QuestionDataTypeId = 0, DisplayOrder = 1, Text = Comfortable }
Table { QuestionDataTypeId = 0, DisplayOrder = 2, Text = attainable }
Table { QuestionDataTypeId = 0, DisplayOrder = 3, Text = recent goal }
Table { QuestionDataTypeId = 0, DisplayOrder = 4, Text = comfortable }
Table { QuestionDataTypeId = 0, DisplayOrder = 5, Text = feeling }

score 1 · Answer 2 · answered Sep 08 '21 at 16:52

I usually use this algorithm


var mult=100000; // You can select a different number depending how many records of 
                 //  the same type  you expecting; Numbers should not overlap

var data = entity
    .OrderBy(m => (m.QuestionDataTypeId*mult + m.DisplayOrder))
    .....

score 1 · Accepted Answer · answered Sep 09 '21 at 01:15

1

Try replace QuestionDataTypeId where value = 0

.OrderBy(x=>x.QuestionDataTypeId==0?int.MaxValue:x.QuestionDataTypeId)
.ThenBy(t=>t.DisplayOrder)

answered Sep 09 '21 at 01:15

FedeFierro

91
1
2

I don't understand what's wrong with simply using `.OrderBy(m => m.QuestionDataTypeId == 0)`. Too bad there's no response to that suggestion. – Gert Arnold Sep 09 '21 at 06:52
My guess is that using boolean comparison results in only two values. The integer value yields a set for ordinal comparison. – Mayur Ekbote Sep 09 '21 at 07:02

linq order by based on multiple columns and various crietria

3 Answers3