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.
- Sort By "PrintOrder" column ascending.
- 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 ?