Select distinct values from a large DataTable column
All credit to Rajeev Kumar's answer, but I received a list of anonymous type that evaluated to string, which was not as easy to iterate over. Updating the code as below helped to return a List that was more easy to manipulate (or, for example, drop straight into a foreach block).
var distinctIds = datatable.AsEnumerable().Select(row => row.Field<string>("id")).Distinct().ToList();
Method 1:
DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "id");
Method 2: You will have to create a class matching your datatable column names and then you can use the following extension method to convert Datatable to List
public static List<T> ToList<T>(this DataTable table) where T : new()
{
List<PropertyInfo> properties = typeof(T).GetProperties().ToList();
List<T> result = new List<T>();
foreach (var row in table.Rows)
{
var item = CreateItemFromRow<T>((DataRow)row, properties);
result.Add(item);
}
return result;
}
private static T CreateItemFromRow<T>(DataRow row, List<PropertyInfo> properties) where T : new()
{
T item = new T();
foreach (var property in properties)
{
if (row.Table.Columns.Contains(property.Name))
{
if (row[property.Name] != DBNull.Value)
property.SetValue(item, row[property.Name], null);
}
}
return item;
}
and then you can get distinct from list using
YourList.Select(x => x.Id).Distinct();
Please note that this will return you complete Records and not just ids.
dt
- your data table name
ColumnName
- your columnname i.e id
DataView view = new DataView(dt);
DataTable distinctValues = new DataTable();
distinctValues = view.ToTable(true, ColumnName);
This will retrun you distinct Ids
var distinctIds = datatable.AsEnumerable()
.Select(s=> new {
id = s.Field<string>("id"),
})
.Distinct().ToList();