Get duplicates for two columns with LINQ

Question

LINQ drives me crazy. Why does following query not return the duplicates, whereas it works with only one identifier? Where is my error?

' 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 > 1
         Select idGroup = New With {grp.Val1, grp.Val2, Group.Count}

Edit: Following is the complete solution, thanks to @Jon Skeet's answer :)

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 > 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

The main problem with grouping was that i must make them key 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.

Select RowNumber = CInt(Group.FirstOrDefault.Item("RowNumber"))

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.

Edit: I'v answered the last part of the question myself, so here is all i need:

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 > 1
         Let Text = Group.First.Item("Text")
         Select Group.First

If dups.Any Then
      Dim dest = dups.CopyToDataTable
End If

I needed the Let-Keyword 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 CopyToDataTable to create a DataTable from the duplicate rows.

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.

score 6 · Accepted Answer · edited Sep 23 '11 at 15:03

6

The problem is the way anonymous types work in VB - they're mutable by default; only Key properties are included for hashing and equality. Try this:

Group row By grp = New With {Key .Val1 = row("Value1"), Key .Val2 = row("Value2")}

(In C# this wouldn't be a problem - anonymous types in C# are always immutable in all properties.)

edited Sep 23 '11 at 15:03

Tim Schmelter

450,073
74
686
939

answered Sep 23 '11 at 14:52

Jon Skeet

1,421,763
867
9,128
9,194

Thank you. I've edited your question because it was too late in the Select, i need it in the first place where i group the rows. I hope i will remember this fact. – Tim Schmelter Sep 23 '11 at 15:04
Yes, the duplicates are correct. But now i'm stuck on the next lines where i want to join the original table with the duplicates. The result(`dupRows`) is the full table(100 rows) and not only the dups(19 rows). – Tim Schmelter Sep 23 '11 at 15:14
The reason for this was that the `Group By` selects only the duplicate rows once. But because i joined them with the original table, i selected also the other duplicate rows. The result was that nearly all rows were in the result table. I needed to select an identifier in the `grouped` rows. Although this works here i'd like to know what i could do if i would have had no `PK` but only composite keys(see my edited question). – Tim Schmelter Sep 23 '11 at 21:53
@TimSchmelter: I'm not quite sure what you mean, but you can use the "Key" of an `IGrouping` to get the whole key - is that what you're after? – Jon Skeet Sep 23 '11 at 21:58
My (theoretical) problem is how to identify rows in a DataTable when i have no identifier but composite keys with duplicate values. The above works because i can select the first duplicate's `RowNumber` value and later select all rows with these identifiers to create a new DataTable from the duplicates. But it would not work when i would `Join On` the two columns, because i would get also the other duplicate rows and not only one. Maybe it's better to create another question from this. – Tim Schmelter Sep 23 '11 at 22:15
@TimSchmelter: I suspect so, yes... and it would probably be better for me to go to bed now, too :) I'll have more chance of grokking the question in the morning... – Jon Skeet Sep 23 '11 at 22:17
It was the Let-Keyword that i was missing(see my last edit). Thanks again. – Tim Schmelter Sep 24 '11 at 12:27

score 0 · Answer 2 · answered Sep 06 '22 at 19:00

What I use to get duplicate rows across two columns in a EF table to show up as duplicates using Lin-q with C Sharp:

            var DuplicatesFoundInTable =
            entities.LocationDatas
           .Where(c => c.TrailerNumber != null && c.CarrierName != null && (c.TrailerNumber ?? string.Empty) != string.Empty && (c.CarrierName ?? string.Empty) != string.Empty)
           .GroupBy(o => new { o.TrailerNumber, o.CarrierName }, l => new { customer.TrailerNumber, customer.CarrierName })
           .Where(g => g.Count() > 1)
           .Select(y => y.Key)
           .ToList();

When I want to see if it is a duplicate on inputs (if the entry already exists in two columns):

          //Check to see if any rows are the same values on TrailerNumber and CarrierName for inputs. 
            bool AlreadyInTableComparer = entities.LocationDatas.Any(l => String.Compare(l.TrailerNumber, customer.TrailerNumber, StringComparison.InvariantCulture) == 0 && String.Compare(l.CarrierName, customer.CarrierName, StringComparison.InvariantCulture) == 0);
            bool AlreadyInTable = entities.LocationDatas.Any(t => t.TrailerNumber == customer.TrailerNumber && t.CarrierName == customer.CarrierName);

SQL Server Checking for duplicates (commented out delete duplicates):

 WITH CTE
 AS
  (
 SELECT [TrailerNumber], [CarrierName]
 ,ROW_NUMBER() OVER(Partition BY TrailerNumber Order by TrailerNumber, 
    CarrierName) AS NumRows, ROW_NUMBER() OVER(Partition BY TrailerNumber, 
   CarrierName Order by CarrierName) AS NumRows2
   FROM [dbo].[LocationData] --Please note, duplicates are shown in this 
   table.
   WHERE  TrailerNumber != '' AND CarrierName != '' 
   )
   SELECT [TrailerNumber], [CarrierName], [NumRows2] FROM CTE WHERE NumRows2 > 1
   --DELETE FROM CTE WHERE NumRows2 > 1  --Delete Duplicates.

Validate SQL Server to prove correct against CTE filtering:

    SELECT TrailerNumber, CarrierName, COUNT(*) AS Duplicates
    FROM [dbo].[LocationData]
    WHERE TrailerNumber IS NOT NULL OR CarrierName IS NOT NULL 
    GROUP BY TrailerNumber, CarrierName
    HAVING COUNT(*) >1 AND TrailerNumber != '' AND CarrierName != ''

Get duplicates for two columns with LINQ

2 Answers2

Linked