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

Tags:

C#

Datatable