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

User · Answer

This will retrun you distinct Ids   var distinctIds   datatable AsEnumerable                        Select s  gt  new                           id   s Field lt string gt   id                                                                            Distinct   ToList

User · Answer

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

User · Answer

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 lt T gt  ToList lt T gt  this DataTable table  where T   new                 List lt PropertyInfo gt  properties   typeof T  GetProperties   ToList            List lt T gt  result   new List lt T gt              foreach  var row in table Rows                        var item   CreateItemFromRow lt T gt   DataRow row  properties               result Add item                      return result             private static T CreateItemFromRow lt T gt  DataRow row  List lt PropertyInfo gt  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   gt  x Id  Distinct      Please note that this will return you complete Records and not just ids

User · Answer

Try this   var idColumn  id   var list   dt DefaultView      ToTable true  idColumn       Rows      Cast lt DataRow gt         Select row   gt  row idColumn        ToList

User · Answer

Sorry to post answer for very old thread  my answer may help other in future   string   TobeDistinct     Name   City   State    DataTable dtDistinct   GetDistinctRecords DTwithDuplicate  TobeDistinct          Following function will return Distinct records for Name  City and State column      public static DataTable GetDistinctRecords DataTable dt  string   Columns                      DataTable dtUniqRecords   new DataTable               dtUniqRecords   dt DefaultView ToTable true  Columns              return dtUniqRecords

User · Answer

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   gt  row Field lt string gt   id    Distinct   ToList

[c#] Select distinct values from a large DataTable column

Examples related to c#

Examples related to datatable