Order by Year and Month in Datatable c#

Question

I am trying to sort the data present in the DataTable by Year in descending order and month in ascending order. But it is not sorting data as expected.

Code used for this as as below

DataTable sortedtTable = currentBalanceData.AsEnumerable().OrderByDescending(x => x.Field<DateTime>("Date").Year).ThenBy(x => x.Field<DateTime>("Date").Month).CopyToDataTable();

and output of the same is below

Let me know what I am doing wrong. Expected output is as below

2017 01

2017 02

2017 03

2017 04

2017 05

2017 06

2017 07

2017 08

2016 01

2016 02

2016 03

2016 04

2016 05

2016 06 ..... and so on.

Edit --> Sorting working only for year.

 DataTable sortedtTable1 = currentBalanceData.AsEnumerable().OrderBy(x => x.Field<DateTime>("Date").Year).ThenBy(y => y.Field<DateTime>("Date").Month).CopyToDataTable();

output of the sortedtTable1 is as below

I dont have an answer... but you are using OrderByDescending and then ThenBy, idk for sure but to me that reads that it would keep the sort direction from the previous call. So call the descending sort, and then the ThenBy would still use a descending direction. I wonder if you need to do it in two separate lines rather than chaining them together. This is ugly but maybe you sort by year and break out each separate year into another list then sort by month, then combine the lists into the master list after those are done. If you dont care about speed or memory give it a try. — Kyle Rone, Sep 22 '20 at 13:39
@KyleRone thanks for the response. As per the details given in below URL we can use ThenBy with the combination of the OrderBy. I followed the same to achieve my expected sorting. https://dotnettutorials.net/lesson/linq-thenby-and-thenbydescending/ — Manoj Naik, Sep 22 '20 at 13:44
Either no repro or I missed something ? https://dotnetfiddle.net/iwQr1k — Drag and Drop, Sep 22 '20 at 13:49
Code looks OK. So, here is my shot in the dark. Just verify what does **Field("date").Month** is returning. I hope it's not "1" always because it might be resolving by "mm/dd/yyyy" format — gkulshrestha, Sep 22 '20 at 13:57
@mjwills I am able to view the sorted datatable by applying order by (ascending) on year. Order by is not working only for Month. — Manoj Naik, Sep 22 '20 at 14:21
Show us the attempt please, so I can compare it to https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/sorting-and-filtering-data?redirectedfrom=MSDN . — mjwills, Sep 22 '20 at 14:22
Resolved. It was not working because of the date format. I assumed it is mm/dd/yyyy but actually it is mm/dd/yyyy. Thanks all for your help. — Manoj Naik, Sep 22 '20 at 14:43

Drag and Drop · Answer 1 · 2020-09-22T14:39:59.847

You might be ordering by something else this datasource, or overwriting it.
Because it Works as you expect it. Like shown in this live demo

var years = Enumerable.Range(2015,2);
var months = Enumerable.Range(1,5);

var dates =     (from y in years
                from m in months
                select new DateTime(y,m,1) // public DateTime (int year, int month, int day);
                 ).ToList();



var ordered = dates.OrderByDescending(d=> d.Year).ThenBy(d=> d.Month);      
ordered.Select(x=> $"{x.Year}/{x.Month}").Dump();
/*
Dumping object(System.Linq.SelectIPartitionIterator`2[DateTime,String])
[
   2016/1          ,
   2016/2          ,
   2016/3          ,
   2016/4          ,
   2016/5          ,
   
   2015/1          ,
   2015/2          ,
   2015/3          ,
   2015/4          ,
   2015/5
]
*/

This match your expected result.

And OrderByDescending, ThenByDescending. matches the grid picture

var ordered2 = dates.OrderByDescending(d=> d.Year).ThenByDescending(d=> d.Month);   
ordered2.Select(x=> $"{x.Year}/{x.Month}").Dump();
/*
Dumping object(System.Linq.SelectIPartitionIterator`2[DateTime,String])
[
   2016/5          ,
   2016/4          ,
   2016/3          ,
   2016/2          ,
   2016/1          ,
   
   2015/5          ,
   2015/4          ,

   2015/3          ,
   2015/2          ,
   2015/1
]
*/

This behavior is consitent even on a DataTable:

DataTable table = new DataTable();
using(var reader = ObjectReader.Create(dates, "Date")) {
    table.Load(reader);
}

var orderedWithDT = table.AsEnumerable().OrderByDescending(x => x.Field<DateTime>("Date").Year).ThenBy(y => y.Field<DateTime>("Date").Month);       
orderedWithDT.SelectMany(x=> x.ItemArray).Dump();

01/01/2016 , 02/01/2016 , 03/01/2016 , 04/01/2016 , 05/01/2016 , 01/01/2015 , 02/01/2015 , 03/01/2015 , 04/01/2015 , 05/01/2015

Order by Year and Month in Datatable c#

1 Answers1