LINQ OrderBy based on row values

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 ?

Jon Skeet
people
quotationmark

The situation is a fairly ugly one, given that two result rows could theoretically be compared which have the same PrintOrder but different AscOrDesc values. It's only the source of the data that's preventing that.

I do have a horrible hack that I believe should work, but I'm really not proud of it. Basically, imagine that the date is a number... ordering by descending date is equivalent to ordering by the negation of the "date number". For DateTime, we can just take the Ticks value, leading to:

var records2 = from q in datatable.AsEnumerable()
               let ticks = q.Field<DateTime>("EffectiveDate").Ticks * 
                   (q.Field<string>("AscOrDesc") == "A" ? 1 : -1)
               orderby q.Field<int>("PrintOrder"), ticks
               select q;

Ugly as heck, but it should work...

people

See more on this question at Stackoverflow