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?

So, you are keeping 10 million rows in a `DataTable` or in the database table? — Alex Filipovici, Jul 04 '13 at 09:10
Your best bet would be to do this on the database before you fetch back the datatable if you have one - it'll be able to do this very efficiently, particularly if the ID is an indexed column. You also haven't said if the data is sorted on the ID column or not. — Rup, Jul 04 '13 at 13:34

score 50 · Accepted Answer · edited Jul 05 '13 at 09:03

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.

score 38 · Answer 2 · answered Jul 04 '13 at 09:12

38

This will retrun you distinct Ids

 var distinctIds = datatable.AsEnumerable()
                    .Select(s=> new {
                        id = s.Field<string>("id"),                           
                     })
                    .Distinct().ToList();

answered Jul 04 '13 at 09:12

Rajeev Kumar

4,901
8
48
83

Perfect, I think this answer is the most flexible. Thank you Rajeev – Joel Jul 02 '18 at 16:42
great!! using datatable to list and make the distinct with linq... – ger Feb 13 '20 at 20:40

score 13 · Answer 3 · answered Jul 04 '13 at 09:13

13

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

answered Jul 04 '13 at 09:13

Veer

1,575
3
16
40

1

this does reduce to distinct values - which method in your code is responsible for the reduction to distinct? – whytheq Jul 11 '15 at 16:45
1

view.ToTable is the method, setting first parameter as true get all the distinct values. – Veer Jul 11 '15 at 20:55
very nice - I've used this. post upped. – whytheq Jul 12 '15 at 11:13
You can omit the second line by writing (in newer C# syntax): var view = new DataView(dt); var distinctValues = view.ToTable(true, ColumnName); You might also want to sort the distinct values by setting the DataViews Sort property before or while you create the DataView object: var view = new DataView(dt) { Sort = "columnName" }; – needfulthing Oct 28 '19 at 14:00

score 6 · Answer 4 · answered Feb 21 '19 at 01:32

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

score 3 · Answer 5 · answered Jul 04 '13 at 09:13

3

Try this:

var idColumn="id";
var list = dt.DefaultView
    .ToTable(true, idColumn)
    .Rows
    .Cast<DataRow>()
    .Select(row => row[idColumn])
    .ToList();

answered Jul 04 '13 at 09:13

Alex Filipovici

31,789
6
54
78

score 2 · Answer 6 · answered Feb 05 '17 at 17:20

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;
       }

Ankur · Answer 7 · 2021-08-10T11:01:28.290

0

Note: Columns[0] is the column on which you want to perform the DISTINCT query and sorting

DataView view = new DataView(DT_InputDataTable);

DataTable distinctValues = new DataTable();

view = new DataView(DT_InputDataTable) { Sort = DT_InputDataTable.Columns[0].ToString() };

distinctValues = view.ToTable(true, DT_InputDataTable.Columns[0].ToString());

edited Aug 10 '21 at 11:01

answered Aug 09 '21 at 11:49

Ankur

11
3

view.ToTable(true, --> this will get all the distinct values of the DT_InputDataTable.Columns[0].ToString() – Ankur Aug 12 '21 at 10:52

Select distinct values from a large DataTable column

7 Answers7

Linked