Select distinct values from a large DataTable column

Question

I have a DataTable with 22 columns and one of the columns I have is called "id". I would like to query this column and keep all the distinct values in a list. The table can have between 10 and a million rows.

What is the best method to do this? Currently I am using a for loop to go though the column and compare the values and if the values are the same then the it goes to the next and when not the same it adds the id to the array. But as the table can have 10 to a million rows is there a more efficient way to do this! How would I go about doing this more efficiently?

Answer 1

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.