score:10
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: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
- 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
- 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))
- Flatten
.SelectMany( g => g );
- Delete rows
rowsToDelete.ForEach( dr => dr.Delete() );
- Accept changes
dataTable.AcceptChanges();
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
score:3
I was solving the same situation and found it quite interesting and would like to share my finding.
- 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
.
- 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();
- 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.
Source: stackoverflow.com
Related Articles
- C# - Remove rows with the same column value from a DataTable
- Remove rows with same column value from DataTable and add corresponding values
- LINQ to remove duplicate rows from a datatable based on the value of a specific row
- Remove multiple keys with same value data from dictionary in C# using Linq
- How to select rows from a DataTable where a Column value is within a List?
- How can I extract all Unique / Distinct Rows from a Datatable and save these rows in a new Datatable with same Columns?
- How to take the Max value from a List of object where the same objects exists with many duplicate rows
- How to delete rows from DataTable with LINQ?
- How to remove an element from an xml using Xdocument when we have multiple elements with same name but different attributes
- Select all rows with distinct column value using LINQ
- howto delete rows from DataTable in C# with a filter?
- get distinct rows from datatable using Linq (distinct with mulitiple columns)
- The given value of type String from the data source cannot be converted to type int of the specified target column
- Remove rows from datatable matching a List<string>
- How to return rows with max value one column grouped by another column?
- Use LINQ to concatenate multiple rows list with same value into single row
- get attribute value from multiple elements with same name and attribute value of one other element in xml
- How to Filter out null rows from DataTable with linq?
- How can I return a list or enumerable of rows where column value has changed with Linq
- Remove rows from a DataTable
- LINQ - Return Value From Field With A Max Value if No Rows Found
- Remove datatable row if any column value is empty c#
- Can't add a new record with an integer value into database by using linq from code C#
- Create multiple DataTable rows based on a column with comma separated values
- Get Max from column datatable or Default Value using Linq
- How to solve cast not valid when Get a Specific Column Value from a DataTable in this example in C#?
- Reading values from a DataTable column into a List<string> with LINQ
- Get specific value from datatable with where-clause
- C# - Copy rows from one DataTable by comparing with 2nd DataTable to 3rd DataTable
- Agregate rows with two or more columns with same value
- Why does a GC after a LINQ query free the WhereListIterator but not the Func representing the condition?
- include null check to find index in list
- Linq Comparing Two Lists and Generating a New One
- LINQ Query for ofType in a related property
- Flip grouping in LINQ
- Save data with foreign key or roll back if any fail
- linq to xml
- Return one value from query C#, EF Core
- LINQ finding the highest frequency
- Is there a better way to group by multiple columns in vb.net
- convert foreach to linq expression
- NHibernate Linq and DistinctRootEntity
- Return data from multiple tables using LINQ Method Syntax
- LinqExpression nested property, compare to string
- Linq to XML - Extract Single Element
- ViewData Int32 must be of type IEnumerable in MVC
- issue selecting descendants in LINQ to XML
- The cast to value type 'Int32' failed because the materialized value is null. Pls help me how to fixed it
- Linq query with toDictionary
- Filter a list on multiple columns