C# - Remove rows with the same column value from a DataTable [duplicate]

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.

2. 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();

3. 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.

you can try this

DataTable uniqueCols = dt.DefaultView.ToTable(true, "ID");

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();

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

2. 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))

3. Flatten

.SelectMany( g => g );

4. Delete rows

rowsToDelete.ForEach( dr => dr.Delete() );

5. Accept changes

dataTable.AcceptChanges();

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

For moreLinq sample code view my blog