how to sort with order by case cluase a data table in c#

Question

i need to sort a datatable according to a case on cell value i tried this:

DataTable dt = new DataTable();
...
dt.DefaultView.Sort = "Case When Col6 = 'open' then 1 When Col6 = 'approved' then 2 When Col6 = 'awaitingApproval' then 3 else 999 end asc";

but this fails. how can i whise a case cluase on datatable like i can write in the sql:

order by 
Case 
   When Col6 = 'open' then 1 
   When Col6 = 'approved' then 2 
   When Col6 = 'awaitingApproval' then 3 
   else 999 
end asc

you can perform [select](https://stackoverflow.com/questions/1990946/datatable-select-with-multiple-conditions) on a datatable — Lucifer, Jul 11 '18 at 12:23
thanks for quick answer, you mean to first select my values with case and then sort them? — chaya D, Jul 11 '18 at 12:29
It fails because [`Sort`](https://msdn.microsoft.com/en-us/library/system.data.dataview.sort(v=vs.110).aspx) is *a string that contains the column name followed by "ASC" (ascending) or "DESC" (descending).* @Lucifer's suggestion should work. — trix, Jul 11 '18 at 12:36

score 2 · Accepted Answer · answered Jul 11 '18 at 12:45

2

How about using linq? This will not actually sort the datatable but return a new sorted collection of rows.

var result = 
    dt.AsEnumerable().OrderBy (x => 
    {
        switch (x.Field<string>("Col6"))
        {
            case "open" : return 1;
            case "approved" : return 2;
            case "awaitingApproval" : return 3;
        }   
        return 999;
    });

answered Jul 11 '18 at 12:45

Magnus

45,362
8
80
118

how is the best way to keep with this and sort according to another column with second priority ? – chaya D Jul 11 '18 at 13:01
@c.d. Add a `.ThenBy(x => )` at the end of the expression. – Magnus Jul 11 '18 at 13:49

score 0 · Answer 2 · answered Jul 11 '18 at 12:36

Create a Calculated Column in the DataTable with this expression:

IIF(Col6='open', 1, IIF(Col6='approved', 2, IIF(Col6='awaitingApproval', 3, 999)))

Then you can easily sort on the calculated column

dt.Columns.Add("CalculatedField", typeof(Int32));
dt.Columns["CalculatedField"].Expression = "IIF(Col6='open', 1, IIF(Col6='approved', 2, IIF(Col6='awaitingApproval', 3, 999)))";

dt.DefaultView.Sort = "CalculatedField";

score 0 · Answer 3 · answered Jul 11 '18 at 12:50

If you want more LinQ-ed way then you can try is

  DataTable dt = new DataTable();
  List<string> sortString = new List<string>();
  sortString.Add("open");
  sortString.Add("approved");
  sortString.Add("awaitingApproval");

  var listsa = dt.AsEnumerable().GroupBy(x => x["Col6"].ToString()).ToList();
  sortString.ForEach(str => listsa.ForEach(x =>
                            {
                              if (x.Key == str)
                                dt.Rows.Add(x.ToList());
                            }));

how to sort with order by case cluase a data table in c#

3 Answers3