CODEWITHSUNDEEP
c#datatableduplicates
darkdante
darkdante

Reputation: 707

How to get records with the same specific column content from a DataTable using LINQ

I have a table with personal data which looks something like this:

 Identifier Name Phone Address
 .............................
     1       aa    23    abc
     2       bb    22    abd
     2       cc    11    aaa
     3       dd    44    amd
     4       fa    33    agd
     2       ds    14    dad
     3       as    55    fgg

I want to get the records with the same identifier, using LINQ, to get something like this

 Identifier Name Phone Address
 .............................
     2       bb    22    abd
     2       cc    11    aaa
     2       ds    14    dad
     3       dd    44    amd
     3       as    55    fgg

I could order by Identifier and copy to a new DataTable, then parse it and get the records with the same identifier, but that would be expensive i guess. Is there a shorter way ? Thank you !

Upvotes: 0

Views: 1630

Answers (2)

Anthony Pegram
Anthony Pegram

Reputation: 126844

Something like the code below would filter and extract the duplicates to a new DataTable with the same schema. Code assumes Identifier is an int. Replace with the appropriate names and types, as applicable.

var extractedDuplicates = (from row in table.AsEnumerable()
                           group row by row.Field<int>("Identifier") into rows 
                           where rows.Count() > 1
                           from row in rows
                           select row).CopyToDataTable();

Give it a try and see how far that gets you. If there is any chance there aren't any duplicates, you will want to split this into multiple statements, as CopyToDataTable() will throw if there are no rows to copy. 

var duplicateRows = from row in table.AsEnumerable()
                    group row by row.Field<int>("Identifier") into rows 
                    where rows.Count() > 1
                    from row in rows
                    select row;

DataTable extractedDuplicates;
if (duplicateRows.Any())
    extractedDuplicates = duplicateRows.CopyToDataTable();
else
    extractedDuplicates = table.Clone();

And, of course, if you do not need a new DataTable, omit the second portion of this code entirely and just work with duplicateRows.

Upvotes: 2

spiritwalker
spiritwalker

Reputation: 2257

select * from YourTable a where a.Identifier in 
(
     select aa.Identifier from YourTable aa group by aa.Identifier 
     having (count(aa.Identifier ) > 1)
)

Upvotes: -1

Related Questions