CODEWITHSUNDEEP
vb.netwinformslinqdatedatagridview
Jami
Jami

Reputation: 11

Display the most recent entry of duplicates based on the Date Column of a DataTable

I have data table with Columns: ID, Chapter Number, Query ID and Date".
I have duplicate questions with same Query ID but different dates.
I want to show, in a DataGridView, only the question (a Row) with the most recent Date when the Query ID is the same.
I also want to display questions (rows) which have not duplicate Query IDs.

My table is like this:

ID Chapter Number Query ID Date (DD-MM-YYYY)
1 Chapter-3 Q-03-05 04-05-2021
2 Chapter-3 Q-03-05 05-05-2021
3 Chapter-3 Q-03-04 06-05-2021
4 Chapter-3 Q-03-06 07-05-2021

I want the table entries in DataGridView like this:

ID Chapter Number Query ID Date (DD-MM-YYYY)
2 Chapter-3 Q-03-05 05-05-2021
3 Chapter-3 Q-03-04 06-05-2021
4 Chapter-3 Q-03-06 07-05-2021

Here is my code:

Dim duplicates = 
   (From cust In SARDataSet.Table1
    Group By dupp = cust.Query_ID Into GP = Group
    Order By dupp Ascending
    Select GP).First()

For Each dw In duplicates
    DataGridView3.DataSource = duplicates.ToList()
Next

Upvotes: 1

Views: 139

Answers (1)

Jimi
Jimi

Reputation: 32278

You're ordering the result based on the Group, you should order in descending order using the Column that stores the Date objects, then take the first one.
Then the loop tries to set the results cast to a List multiple times. You clearly don't want that.

Assuming the Column is actually storing dates and not strings.
If you stored strings, change the Column Type to DateTime (Date), otherwise ordering this Column won't ever achieve the desired results.

Add a reference to System.Data.DataSetExtension in the unfortunate case you don't have it already in your Project. I'm naming the Date Type Column QueryDate since I don't know its actual name.

Here, I'm generating a new DataTable that contains the filtered results and set it as the DataSource of your DataGridView:

Dim dtNew = SARDataSet.Table1.AsEnumerable().
    GroupBy(Function(dr) dr("Query ID")).
    Select(Function(grp) 
        grp.OrderByDescending(Function(dr) dr("QueryDate")).First()).
    CopyToDataTable()

' In case a source of data is already assigned
DataGridView3.DataSource = Nothing
' Set the new data
DataGridView3.DataSource = dtNew

What the LINQ code is doing:

  • AsEnumerable(): reads a DataTable as a collection of DataRows objects
  • GroupBy() => dr("Query_ID"): Groups the DataRows objects based on the value of the Query_ID Column
  • Select(Function(grp): enumerates the generated Groups and...
  • grp.OrderByDescending(Function(dr) dr("QueryDate")) ...orders the DataRows in each Group by the Date Column in descending order (most recent Date first) then...
  • First(): ...takes the first DataRow in the ordered list
  • CopyToDataTable(): converts the resulting IEnumerable(Of DataRow) to a new DataTable object.

Upvotes: 2

Related Questions