Return DataTable with Duplicates using LINQ to DataTables

[charlybones]

Ok so I'm a total noob with VB.NET and I so far I've read about a thousand forums and I've yet to get a straight answer.

I've read that VB treats anonymous types differently than C# (the why, beats me).

I have a DataTable, and I want to return a NEW Table with the duplicate entries (of columns 4 and 5) from the first one.

For ex:

T1 looks like this:

[COL4][COL5][COL6]...[COLN]
1         1 
1         2 
1         1 
1         3 
1         3 

The Output Table should be like this:

    [COL4] [COL5]
     1         1
     1         3

So far I've got this:

Dim duplicates = dt.AsEnumerable() _
            .GroupBy(Function(r) New With { _
             Key .a = r(4), _ 'Column 4 and Column 5 are the patters that I want to check
             Key .b = r(5) _
            }).Where(Function(gr) gr.Count() > 1)

The problem is reading the data. I can't cast to ANYTHING, I can't even read the results.

I can't read the 'duplicates' variable.

I don't care about the rest of the data columns, I don't have to have the whole DataRow as a result, I just want those two columns (4 and 5) for the items that are repeated.

Hope anyone can help me solve this.

[Begginnerdev]

You could try to create a linq table in your dbml file named 'Duplicate' with the same column names and field lengths.

Then, try to cast the object to the type of Duplicate

[charlybones]

I tried, no dice. I finally solved the issue with a somewhat dirty hack...

See the code:

Public Function GetTableDuplicates(ByVal table As DataTable) As DataTable
            Dim doubles As New DataTable 'Result table 
            Dim aRow As DataRow

            doubles.Columns.Add(New DataColumn("Column4Name", GetType(String)))  
            doubles.Columns.Add(New DataColumn("Column5Name", GetType(String)))

            Dim duplicates = From rows In table.AsEnumerable() _
                            .GroupBy(Function(r) New With { _
                            Key .a = r(4), _
                            Key .b = r(5) _
                            }).Where(Function(gr) gr.Count() > 1).ToList()


            For Each item As Object In duplicates 'not so proverbial, dirty hack here. 
                For Each r As Object In item  
                    aRow = doubles.NewRow()
                    aRow(0) = r(4).ToString()
                    aRow(1) = r(5).ToString()
                    doubles.Rows.Add(aRow)
                Next
            Next
            Return doubles.DefaultView.ToTable(True, "Column4Name", "Column5Name")
        End Function

The thing is, this works. But coming from C#... I've no idea why it works and why I can't explicitly access the new Object's members the same way I do in C#.

Anyways, thanks for the suggestion. This is an in-memory table, the project I'm currently working in doesn't even have a database.

Thanks again.

[Begginnerdev]

This works by first selecting the rows from the table, them storing them into a linq query.

You then cycle through the linq query and add new rows to the duplicates table (Just iterating on objects).

One other thing you can possibly do, is to write a custom class to contain the duplicate and create a list for the duplicates.

For example:

Public Class Duplicates
    Private _ID As Integer = -1
    Private _Field1 As String = String.Empty
    Private _Field2 As String = String.Empty

    Public Property ID As Integer
        Get
            Return Me._ID
        End Get
        Set(value As Integer)
            Me._ID = value
        End Set
    End Property

    Public Property Field1 As String
        Get
            Return Me._Field1
        End Get
        Set(value As String)
            Me._Field1 = value
        End Set
    End Property

     Public Property Field2 As String
        Get
            Return Me._Field2
        End Get
        Set(value As String)
            Me._Field2 = value
        End Set
    End Property

    Public Sub New()
    End Sub

    Public Sub New(ByVal iID as Integer)
        If iID > -1 Then Me.ID = iID
    End Sub

    Public Sub New(ByVal iID as Integer, ByVal sField1 As String)
        If iID > -1 Then Me.ID = iID
        If Not String.IsNullorEmpty(sField1) Then Me.Field1 = sField1
    End Sub

    Public Sub New(ByVal iID as Integer, ByVal sField1 As String, ByVal sField2 As String)
        If iID > -1 Then Me.ID = iID
        If Not String.IsNullorEmpty(sField1) Then Me.Field1 = sField1
        If Not String.IsNullorEmpty(sField2) Then Me.Field2 = sField2
    End Sub

End Class

Then you can iterate through the LINQ query and pass the values to the Duplicates Class:

Dim lstDups As New List(of Duplicates)

For Each t As (type) In linqQuery
    lstDups.Add(New Duplicates(t.ID,t.Field1,t.Field2))
Next

'lstDups will now contain every duplicate.