Select distinct DataTable rows

Question

I have a DataTable where sometimes values in all columns in two or more rows repeat. I would like to get distinct DataTable. The solutions from here and here don't work for me because I have many columns and depending on some conditions, the number of columns changes.

I was thinking maybe something like this

System.Data.DataTable table = new System.Data.DataTable(); // already fulfilled table

DataView view = new DataView(table);
var tableDistinct = view.ToTable(true, table.Columns);

But I can't pass table.Columns as an argument.

I guess in your case its the backend sql query which need to be modified rather the dataview. So, use distinct in your sql query and see if your getting distinct rows. — Sonal Borkar, Dec 17 '18 at 14:53
I can't edit sql query because during creating datatable I make conditions/ change names in rows so that at the end I am receiving duplicated rows — user0810, Dec 17 '18 at 14:55

Tim Schmelter · Answer 1 · 2018-12-17T14:57:48.383

3

I don't know what's going wrong because you haven't said what's not working. However, you could use LINQ(-TO-DataTable):

table = table.AsEnumerable()
    .GroupBy(r => new{ Col1 = r["Col1"], Col2 = r["Col2"], Col3 = r["Col3"] })
    .Select(g => g.First())
    .CopyToDataTable();

Change the columns in the anonymous type according to your column-list.

edited Dec 17 '18 at 14:57

answered Dec 17 '18 at 14:55

Tim Schmelter

450,073
74
686
939

For your answer or for the original question? – npo Dec 17 '18 at 15:00

score 1 · Accepted Answer · answered Dec 17 '18 at 14:56

1

The ToTable access a list of string params, the following should convert all your columns to array of string so you don't have to enter them manually

 System.Data.DataTable table = new System.Data.DataTable(); // already fulfilled table

            DataView view = new DataView(table);
            var tableDistinct = view.ToTable(true, table.Columns.Cast<DataColumn>().Select(z=>z.ColumnName).ToArray());

answered Dec 17 '18 at 14:56

npo

1,060
8
9

It works, thanks, but I also noticed that without passing this second parameter, only `table = view.ToTable(true);` still works. Do you think I could leave the code this way, or it is better to give second parameter? – user0810 Dec 17 '18 at 15:09
1

Really, not sure about that to be honest – npo Dec 17 '18 at 15:10

Select distinct DataTable rows

2 Answers2