title: Get duplicates for two columns with LINQ
body: <p>LINQ drives me crazy. Why does following query not return the duplicates, whereas it works with only one identifier? Where is my error?</p>
  
  <pre><code>' generate some test-data '
  Dim source As New DataTable
  source.Columns.Add(New DataColumn("RowNumber", GetType(Int32)))
  source.Columns.Add(New DataColumn("Value1", GetType(Int32)))
  source.Columns.Add(New DataColumn("Value2", GetType(Int32)))
  source.Columns.Add(New DataColumn("Text", GetType(String)))
  Dim rnd As New Random()
  For i As Int32 = 1 To 100
      Dim newRow = source.NewRow
      Dim value = rnd.Next(1, 20)
      newRow("RowNumber") = i
      newRow("Value1") = value
      newRow("Value2") = (value + 1)
      newRow("Text") = String.Format("RowNumber{0}-Text", i)
      source.Rows.Add(newRow)
  Next
  ' following query does not work, it always has Count=0 '
  ' although it works with only one identifier '
  Dim dupIdentifiers = From row In source
           Group row By grp = New With {.Val1 = row("Value1"), .Val2 = row("Value2")}
           Into Group
           Where Group.Count &gt; 1
           Select idGroup = New With {grp.Val1, grp.Val2, Group.Count}
  </code></pre>
  
  <hr>
  
  <p><strong>Edit</strong>: Following is the complete solution, thanks to <a href="https://stackoverflow.com/questions/7530906/get-duplicates-for-two-columns-with-linq/7530944#7530944">@Jon Skeet's answer</a> :)</p>
  
  <pre><code>Dim dupKeys = From row In source
          Group row By grp = New With {Key .Val1 = CInt(row("Value1")), Key .Val2 = CInt(row("Value2"))}
          Into Group Where Group.Count &gt; 1
          Select RowNumber = CInt(Group.FirstOrDefault.Item("RowNumber"))
  
  Dim dupRows = From row In source
          Join dupKey In dupKeys 
          On row("RowNumber") Equals dupKey 
          Select row
  
  If dupRows.Any Then
      ' create a new DataTable from the first duplicate rows '
      Dim dest = dupRows.CopyToDataTable
  End If
  </code></pre>
  
  <p>The main problem with grouping was that i must make them <code>key</code> properties.
  The next problem in my above code was to get the duplicate rows from the original table.
  Because nearly every row has a duplicate(according to two fields), the result DataTable contained 99 of 100 rows and not only the 19 duplicate values.  I needed to select only the first duplicate row and join them with the original table on the PK.</p>
  
  <pre><code>Select RowNumber = CInt(Group.FirstOrDefault.Item("RowNumber"))
  </code></pre>
  
  <p>Although this works in my case, maybe someone can explain me how to select only the duplicates from the original table if i would have had only composite keys.</p>
  
  <hr>
  
  <p><strong>Edit</strong>: I'v answered the last part of the question myself, so here is all i need:</p>
  
  <pre><code>Dim dups = From row In source
           Group By grp = New With {Key .Value1 = CInt(row("Value1")), Key .Value2 = CInt(row("Value2"))}
           Into Group Where Group.Count &gt; 1
           Let Text = Group.First.Item("Text")
           Select Group.First
  
  If dups.Any Then
        Dim dest = dups.CopyToDataTable
  End If
  </code></pre>
  
  <p>I needed the <a href="https://stackoverflow.com/questions/641931/is-linqs-let-keyword-better-than-its-into-keyword/641944#641944">Let-Keyword</a> in order to keep the other column(s) into the same context and return only the first row of the grouped dups. On this way i can use <code>CopyToDataTable</code> to create a DataTable from the duplicate rows.</p>
  
  <p>Only a few lines of code overall (i can save the second query to find the rows in the original table) to find duplicates on multiple columns and create a DataTable of them.</p>