LINQ to DataTable, finding duplicate rows

Question

Using the following DataTable:

    Dim d As New DataTable()
    d.Columns.Add("Product", GetType(System.String))
    d.Columns.Add("Value", GetType(System.Double))

    d.Rows.Add(New Object() {"OAP1", 100.0})
    d.Rows.Add(New Object() {"EPP4", 100})
    d.Rows.Add(New Object() {"OAP1", 150.25})
    d.Rows.Add(New Object() {"OAPIN", 200.0})

I'm trying to use LINQ to identify if there are more than one of any type of product. In SQL, this would work something like:

SELECT Product FROM SOME_TABLE HAVING COUNT(*) > 1

I can't for the life of me work out how to do this in LINQ. I was following someone who did something like this:

    Dim q = From row In d.AsEnumerable()
            Group row By New { column1 = row("Product"), column2 = row("Value") }
            Into grp
            Where grp.Count() > 1
            Select row

    For Each item In q
        ' 'q' is not declared. It may be inaccessible due to its protection level.
    Next

But I get an error when I try to actually use 'q'. Any ideas on how I can make this work?

This was what I was following: thereforesystems.com/find-duplicates-using-linq — Dan Champagne
– Dan Champagne, Commented Jul 5, 2011 at 14:16
You want to group on both Product and value? With your sample data that wont give you any duplicates. — Magnus
– Magnus, Commented Jul 5, 2011 at 14:40
You're right. I've edited the SQL query to reflect what I actually want. I want to find duplicate Product types. — Dan Champagne
– Dan Champagne, Commented Jul 5, 2011 at 14:59

Andrew Peacock · Accepted Answer · 2011-07-05 14:56:10Z

6

Try this

Dim duplicates = d.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("Product")).Where(Function(g) g.Count() > 1).Select(Function(g) g.Key)

For Each dup In duplicates
Next

answered Jul 5, 2011 at 14:56

Andrew Peacock

7715 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

msarchet · Accepted Answer · 2011-07-05 14:25:44Z

1

Just try

Dim q = From row in d.AsEnumerable()
        Group row By row.Field(Of String)("Product")
        Into grp
        Where grp.Count() > 1
        Select { Country = row("Product"), Value = row("Value") }

edited Jul 5, 2011 at 14:25

answered Jul 5, 2011 at 14:20

msarchet

15.3k2 gold badges45 silver badges66 bronze badges

2 Comments

Dan Champagne Over a year ago

I tried that, and got the following error: Range variable name can be inferred only from a simple or qualified name with no arguments.

msarchet Over a year ago

Try using the field extension method.

Bala R · Accepted Answer · 2011-07-05 14:50:34Z

0

I'm not sure about query syntax with VB.NET by try this

Dim q = d.AsEnumerable().Select(Function(r) New With { .Column1 = r("Product"), .Column2 = r("Value")}) _
                    .GroupBy(Function(r) New With {r.Column1, r.Column2}) _
                    .Where(Function(g) g.Count() >1 )

For Each item In q
    Console.WriteLine(item.Key)
Next

answered Jul 5, 2011 at 14:50

Bala R

109k23 gold badges203 silver badges212 bronze badges

Collectives™ on Stack Overflow

LINQ to DataTable, finding duplicate rows

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related