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

Question

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 !

Can we have some code, please? What have you tried? When you say 'datatable' are you referring to the System.Data.DataTable? Or a text file with data in the format you've provided? — IAbstract
– IAbstract, Commented Mar 28, 2013 at 3:15
Yes, it is a System.Data.DataTable. I didn;t post any code because it is big and i have completely different columns. I already extracted duplicates, then i was thinking of making a difference between the data tables, but i couldn;t figure it out. Thanks for the answer. — darkdante
– darkdante, Commented Mar 28, 2013 at 3:35

Anthony Pegram · Accepted Answer · 2013-03-28 03:35:57Z

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.

spiritwalker · Accepted Answer · 2013-03-28 03:21:28Z

-1

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

answered Mar 28, 2013 at 3:21

spiritwalker

2,26715 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related