SELECT Unique rows from Datagridview using LINQ

Question

I am trying to SELECT ALL rows\columns from a datagridview where the first column is unique using LINQ.

Datagridview:

1     Blue     1111
1     Blue     1111
2     Green    1234
3     Orange   3211
2     Green    1234
4     Red      2222

Trying to get this Output:

1     Blue     1111
2     Green    1234
3     Orange   3211
4     Red      2222

I was able to use the following code which pulls all unique records from the first column but I am not sure how to get the remaining columns:

        Dim unique() As String = (From row As DataGridViewRow In dgvMaestro.Rows.Cast(Of DataGridViewRow)() _
         Where Not row.IsNewRow _
         Select CStr(row.Cells(0).Value)).Distinct.ToArray

        For Each a As String In unique
            Debug.Print(a)
        Next

Output:

Thanks

You're saying `Select CStr(row.Cells(0).Value))` - That would only give you the value of the first element (hence 1,2,3,4)... You could return the row by typing `Select row`, but you still need a way to output it then.... — John Bustos, Nov 18 '13 at 21:21
I think that's where I am struggling. I am still trying to familiarize myself with linq but any changes I make to call additional columns breaks the query. If I just specify row then it doesn't let me include the distinct. — Force, Nov 18 '13 at 21:52

score 3 · Accepted Answer · edited Feb 27 '14 at 15:06

First you need import 2 namespaces for distinct with linq to use AsEnumerable() method in DataTables and Field() method in DataRows

Imports System.Data.DataTableExtensions
Imports System.Data.DataRowExtensions

Declare new datatable

Dim NewTbl As New System.Data.DataTable

Add columns in you scenario

NewTbl.Columns.Add("ID", GetType(Integer))
NewTbl.Columns.Add("Color", GetType(String))
NewTbl.Columns.Add("Value", GetType(Integer))

Linq use Table 'TblValues' as you datasource

Dim results = (From row in TblValues.AsEnumerable() select col1 = row(Of Integer)("ID"), col2 = row(Of String)("Color"), col3 = row(Of Integer)("Value")).Distinct().ToList()

Iterate elements in results with for each, the reason is because results is an object datarow collection and isn't convert auto to table

For each r in results
  Dim Row as System.Data.DataRow = NewTbl.NewRow
  Row("ID") = r.col1
  Row("Color") = r.col2
  Row("Value") = r.col3
  NewTbl.Rows.Add(Row)
next

Now you have a DataTable 'NewTbl' with distinct values inside

score 0 · Answer 2 · answered Nov 19 '13 at 00:24

0

The best way to solve this is to write a comparer to compare the entire row.

Dim noduplicates = dgvMaestro.Rows.Cast(Of DataGridViewRow).Distinct(New RowComparer())

There are some examples of comparers on msdn

answered Nov 19 '13 at 00:24

James Ferretti

171
2
15

SELECT Unique rows from Datagridview using LINQ

2 Answers2

Linked