score:10

Accepted answer

you can use linq to datatable, to distinct based on column id, you can group by on this column, then do select first:

  var result = dt.asenumerable()
                 .groupby(r => r.field<int>("id"))
                 .select(g => g.first())
                 .copytodatatable();

score:1

heres a way to achive this, all you need to use morelinq library use its function distinctby

code:

protected void page_load(object sender, eventargs e)
{
  var distinctbyidcolumn = getdt2().asenumerable()
                                   .distinctby(
                                   row => new { id = row["id"] });
  datatable dtdistinctbyidcolumn = distinctbyidcolumn.copytodatatable();
}


public datatable getdt2()
{
   datatable dt = new datatable();
   dt.columns.add("id", typeof(string));
   dt.columns.add("name", typeof(string));
   dt.columns.add("dob", typeof(string));
   dt.rows.add("1", "aa","1.1.11");
   dt.rows.add("2", "bb","2.3.11");
   dt.rows.add("2", "cc","1.2.12");
   dt.rows.add("3", "cd","2.3.12");
   return dt;
}

output: as what you expected

enter image description here

for morelinq sample code view my blog

score:2

you can try this

datatable uniquecols = dt.defaultview.totable(true, "id");

score:2

not necessarily the most efficient approach, but maybe the most readable:

table = table.asenumerable()
    .groupby(row => row.field<int>("id"))
    .select(rowgroup => rowgroup.first())
    .copytodatatable();

linq is also more powerful. for example, if you want to change the logic and not select the first (arbitrary) row of each id-group but the last according to datebirth:

table = table.asenumerable()
    .groupby(row => row.field<int>("id"))
    .select(rowgroup => rowgroup
                          .orderbydescending(r => r.field<datetime>("datebirth"))
                          .first())
    .copytodatatable();

score:2

  1. get a record count for each id
var rowstodelete = 
    (from row in datatable.asenumerable()
    group row by row.id into g
    where g.count() > 1
  1. determine which record to keep (don't know your criteria; i will just sort by dob then name and keep first record) and select the rest
select g.orderby( dr => dr.field<datetime>( "datebirth" ) ).thenby( dr => dr.field<string>( "name" ) ).skip(1))
  1. flatten
.selectmany( g => g );
  1. delete rows
rowstodelete.foreach( dr => dr.delete() );
  1. accept changes
datatable.acceptchanges();

score:3

i was solving the same situation and found it quite interesting and would like to share my finding.

  1. if rows are to be distinct based on all columns.
datatable newdatatable = dt.defaultview.totable(true, "id", "name", "datebirth");

the columns you mention here, only those will be returned back in newdatatable.

  1. if distinct based on one column and column type is int then i would prefer linq query.
  datatable newdatatable = dt.asenumerable()
                           .groupby(dr => dr.field<int>("id"))
                           .select(dg => dg).take(1)
                           .copytodatatable();
  1. if distinct based on one column and column type is string then i would prefer loop.
list<string> toexclude = new list<string>();
for (int i = 0; i < dt.rows.count; i++)
{
    var idvalue = (string)dt.rows[i]["id"];
    if (toexclude.contains(idvalue))
    {
        dt.rows.remove(dt.rows[i]);
        i--;
    }
    toexclude.add(glaccount);
}

third being my favorite.

i may have answered few things which are not asked in the question. it was done in good intent and with little excitement as well.

hope it helps.


Related Query

More Query from same tag