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: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: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: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 Query
- 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
- How to take the Max value from a List of object where the same objects exists with many duplicate rows
- The given value of type String from the data source cannot be converted to type int of the specified target column
- 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?
- multiple records with the same order id incremented from the previous MAX value
- Add multiple rows into datatable with the same data without looping
- What's The Best Way To Remove Duplicate Rows Based On A Row Item In A DataTable With VB.Net?
- How do I combine data from two rows with the same foreign key in sql/linq
- Creating a datatable with only the rows that match a specific column name prefix using Linq in c#
- How I get with a attribute value from a xml the other attribute values from the same node?
- How to get how many rows that has a column with the same record
- Linq to return all rows with the same (lowest) value
- Populate List<string> with the same value with LINQ
- 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
- LINQ-to-Entities, Ambiguous Column Name with association between two views with the same column name
- C# LINQ Select objects with the same value of one property join values of other
- Select all rows with distinct column value using LINQ
- howto delete rows from DataTable in C# with a filter?
- LINQ left join with only the row having maximum value of a column
- How can I group by the difference between rows in a column with linq and c#?
- Create a sequence of anonymous types from the rows in a DataTable
- get distinct rows from datatable using Linq (distinct with mulitiple columns)
- Get from a List<t> the record with the maximum value of a specific property
- When called from 'VisitLambda', rewriting a node of type 'System.Linq.Expressions.ParameterExpression' must return a non-null value of the same type
- Remove rows from datatable matching a List<string>
More Query from same tag
- How to use linq to assign two lists and an int to create one list of a new object?
- Parse LINQ answer in a JSON
- Get the first record of a group in LINQ?
- Is there a Match like syntax for LINQ
- How to filter a list within another list MVC LINQ
- Unable to create a constant value of type error with recursive func
- Is LINQ implementation of Entity Framework inappropriate?
- How to get list of keys in a dictionary with group by value with using LinQ
- How to rewrite C# Linq query with state variable into T-SQL
- Why does this LINQ query fail to work?
- Searching in multi-level tree structure
- EF LInq Left outer join sorted taking fist
- is it possible to have a select statement in the resultset of a linq query?
- Multiple join with groupby in linq
- Linq Expression throws InvalidOperationException
- How to get a column which type is float using linq to entities
- Dynamic Linq on method called with generic type via reflection
- how to create a list of lists from one list using LINQ?
- SQL data context with inner join
- Linq vb.net - where X IN (SELECT ...)
- Entity Framework Check if multiple records exist before insert
- Linq mixed with string.Compare(...,...,...)
- Dynamic where condition LINQ
- Why Linq query doesn't return records when querying against partition key in Cosmos Db?
- XDocument To Read MetaBase.xml
- How do I generate a dbml file?
- Group by a range of value with datatable using linq
- What is the best way to convert this SQL query to Linq?
- how to select columns from two table with where using linq
- LINQ to SQL - How to "Where ... in ..."