LINQ OrderBy based on row values

Viewed 2601

Lets say we have two tables Parent "DocumentCodes" and Child "Documents". DocumentCodes table have columns DID,DocumentName,PrintOrder and AscOrDesc Documents table have columns ID,DID and EffectiveDate.We are getting datatable by joining these two tables.

We need to sort this datatable based on below rules.

  1. Sort By "PrintOrder" column ascending.
  2. If two or more rows have similar "DocumentNames" value then sort by "EffeciveDate" ascending or descending based on "AscOrDesc" value. "AscOrDesc" column accepts only 'A' or 'D'. If value is 'A' we need to sort "EffectiveDate" ascending and if value is 'D' we need to sort "EffectiveDate" descending.

For example,

DocumentCodes

 DID     DocumentName       PrintOrder      AscOrDesc
 1        Test1               1               D
 2        Test2               2               A
 3        Test3               3               D

Documents

ID        DID        EffectiveDate    
 1         2           7/9/2017
 2         1           5/5/2017
 3         2           7/8/2017
 4         3           4/9/2017

After joining above two tables. We have DataTable.

ID      DocumentName EffectiveDate  PrintOrder  AscOrDesc
1         Test2        7/9/2017       2          A
2         Test1        5/5/2017       1          D
3         Test2        7/8/2017       2          A 
4         Test3        4/9/2017       3          D

Now After sorting this DataTable by using above rules. DataTable should look like this.

ID      DocumentName EffectiveDate  PrintOrder  AscOrDesc
1         Test1         5/5/2017      1           D
2         Test2         7/8/2017      2           A
3         Test2         7/9/2017      2           A
4         Test3         4/9/2017      3           D

Note: EffectiveDate is in MM/DD/YYYY format.

I tried with below code but its not working.

var records2 = from q in datatable.AsEnumerable()
               let sortorder= q.Field<string>("AscOrDesc") == "A" ?
               "q.Field<DateTime>(\"EffectiveDate\") ascending": 
               "q.Field<DateTime>(\"EffectiveDate\") descending"
               orderby q.Field<int>("PrintOrder"),sortorder
               select q;

what I am doing wrong in above code ?

3 Answers
Related