Select Duplicates in a DataTable

Hi All.

I am struggling to select records in a DataTable where there are duplicates.
Here is a sample data in my DataTable.
image

Here is the code I am trying to run to retrieve duplcate records, but somehow its not working.

dt_duplicateRecords = (From d In dtOutput
Group d By k1=d(2).toString.Trim, k2=d(3).toString.Trim,k3=d(5).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

Can someone please assist? I want to be able to select all records including the duplicates, so in the above example first 2 rows

Hi @sacad

You can try with below expression

(From p in DT.Select() where( From q in DT.Select() where q("ID").Equals(p("ID)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Regards
Gokul

Hi @sacad

Use below function!
Assign

DuplicateRecordDt =ReadDt.Select("[FamilyName] =‘TestA’").CopyToDatatable

Regards

1 Like

Hi @pravin_calvin ,

Try this out,

 dt_duplicateRecords  = (From d In dtOutput.AsEnumerable()
Let cnt = dtOutput.AsEnumerable.Where(Function (r) d.ItemArray.SequenceEqual(r.ItemArray)).Count
Where cnt > 1
Select d).copydatatable

Regards,

your initial statement could work, but maybe fails on on name variations. Compared to the real data, what is the issue, what are the fails?

Thanks for your response.

Ive run this against a large datatable containing about 200 records where about 15 records containing duplicates. For example one client had about 5 records.
This query only returned 2 records

Does this query only returns exact duplicates for all columns?

I want to check duplicates for 3 columns only and ignore the rest

Hi,

Can you share specific sample data as a file? It’s no problem if dummy data.

Regards,

Here is a subset of a large dataset. This should be enough as there is lots of duplicates

Hi,

Thank you for sharing. but can you share this as an excel file? It’s OK if there are several rows like your first post.

Regards,

Sorry i dont have it in excel

(From d In dtOutput.AsEnumerable
Group d By k1=d(2).toString.Trim, k2=d(3).toString.Trim,k3=d(5).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable

Give a try on this statement. AsEnumerable was added on the first line

Hi @sacad ,

try this,

(From p in dtOutput.Select() where( From q in dtOutput.Select() where q("column1").Equals(p("column1)) and q("column2").Equals(p("column2)) and q("column3").Equals(p("column3)) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

Thanks, this is overall working

However i want to search using a specific column, how can i do that please?

I want to be able to search using ID = 123456 but this isnt working somehow

For example:

(From p In in_DataTable.Select() Where( From q In in_DataTable.Select()
Where q(“ID”).Equals(“123456”) And q(“FN”).Equals(p(“FN”)) And q(“PT”).Equals(p(“PT”)) Select q).ToArray.Count>1
Select p).ToArray.CopyToDataTable()

HI @sacad ,

With regards to your question, so, when use a LINQ query, the part where, q("column1").Equals(p("column1"), we are collectively comparing the whole columns. Here since your trying to compare column with a single ID, it will throw you error.

As per my understanding on your question, you want to search the ID = 123456 , In the datatable, Am i right ? if not please explain more on this.

If Yes, you can try a simple select condition,

step1 : if (in_DataTable.select("[ID]='123456'").Length >1) then
step2 : in_DataTable = in_DataTable.select("[ID]='123456'").copytodatatable()
step3 : Nothing
2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.