0

I have duplicate values which are in this format in a data table 

Sak    Dte
1      12/31/2020
2      12/31/2020
3      04/01/2020
3      12/31/2020

I need to remove Sak 3 with 04/01/2020 value from the data table . Below is the code I am using but it is removing Sak 3 with 12/31/2020 values any ideas how to solve this issue. Note: If you find similar sak value you need to remove the past date (dte)value and keep the future date (dte)value .ex: sak 3 has 04/01/2020 and 12/31/2020 in this we need to remove 04/01/2020 and keep 12/31/2020 Expected O/P Sak Dte 1 12/31/2020 2 12/31/2020 3 12/31/2020 

private DataTable RemoveDuplicateRows(DataTable table)
{
    List<DataRow> rmList = new List<DataRow>();
    Dictionary<long, long> dictionary = new Dictionary<long, long>();

    foreach (DataRow row in table.Rows)
    {
        long sak = Convert.ToInt64(row["Sak"]);
        if (dictionary.ContainsKey(sak) == false)
        {
            dictionary.Add(sak, sak);
        }
        else
        {
            rmList.Add(row);
        }

    }
    foreach (DataRow row in rmList)
    {
        table.Rows.Remove(row);
    }

    //Datatable which contains unique records will be return as output.
    return table;
}
Avi
  • 1
  • 1
  • Mind sharing the expected result? How do you know that you have to keep `12/31/2020` and remove `04/01/2020`? Are we taking the latest data for each "sak"? or do we filter the list on the latest data(not the same)? Is that really because of the latest or most occuring? – Drag and Drop May 04 '20 at 12:49
  • If i understand correctly you need a simple groupby on Sak, order the List Dte and take the last one. if that the case then this is your duplicate : [How to select last record in a LINQ GroupBy clause](https://stackoverflow.com/questions/38304710/how-to-select-last-record-in-a-linq-groupby-clause) – Drag and Drop May 04 '20 at 12:52
  • Does this answer your question? [How to select last record in a LINQ GroupBy clause](https://stackoverflow.com/questions/38304710/how-to-select-last-record-in-a-linq-groupby-clause) – Drag and Drop May 04 '20 at 12:55
  • Expected Result Sak Dte 1 12/31/2020 2 12/31/2020 3 12/31/2020 . We need to keep future date values an remove past date value if they have similar sak values like sak 3 – Avi May 04 '20 at 14:19
  • I will recommend using a bigger test case covering every case you have with a clear explanation of each case. You question title, body, and comment all point in 3 different direction. Something more like that https://pastebin.com/9KEdrikp – Drag and Drop May 04 '20 at 14:57

1 Answers1

0

Try this :

table = table.AsEnumerable()
       .GroupBy(r => r["datetime"])
       .Select(g => g.OrderByDescending(c => c["datetime"]).FirstOrDefault())
       .CopyToDataTable();

I don't know name of your date time column. Not sure if it is Dte so i simply wrote there "datetime".

Samuel B.
  • 156
  • 5