using distinct in DataTable.Select function

Question

I have a data table and I want to populate two extra datatables using this datatable,here is a simple form of my table

My data table columns are 
[name][family][id][propertyid][propertyEnergy]
John smith 1 12 Gas
John smith 1 13 Gas
John smith 1 14 null
John smith 1 15 Gas
Hannah smith 2 16 Gas
Hannah smith 2 17 Gas
Hannah smith 2 18 Gas

I want to use this query in datatable select distinct [name][family][id] from table which results

John smith 1
Hannah smith 2

and again I use this query in another datatable select [id][propertyid][propertyEnergy] from table which results

1 12 Gas
1 13 Gas
1 14 null
1 15 Gas
2 16 Gas
2 17 Gas
2 18 Gas

I searched and found that I can DataTable.Select but examples that I have seen shows that I can only add Where sentense to DataTable.Select and I have no idea how to perform things like Distinct in it, Can you please help me or give me some hints how to do it? Thank you so much

Helpful link http://stackoverflow.com/questions/1199176/how-to-select-distinct-rows-in-a-datatable-and-store-into-an-array — Sybren, Nov 27 '14 at 10:08

Tim Schmelter · Answer 1 · 2014-11-27T10:17:54.290

1

I'd use Linq-To-DataTable instead:

var distinctNames = table.AsEnumerable()
    .Select(row => new
    {
        Name = row.Field<string>("Name"),
        Family = row.Field<string>("Family"),
        ID = row.Field<int>("ID")
    })
    .Distinct();

var distinctProperties = table.AsEnumerable()
    .Select(row => new
    {
        ID = row.Field<int>("ID"),
        PropertyID = row.Field<int>("PropertyID"),
        PropertyEnergy = row.Field<int>("PropertyEnergy")
    })
    .Distinct();

If you need two additional DataTables you have to create and fill them manually since the columns are different than the main-table. You can fill them in a loop from the queries above.

This should work as it is:

string[] nameColumns = { "Name", "Family", "ID" };
DataTable tblNames = table.Clone();
var removeColumns = tblNames.Columns.Cast<DataColumn>()
    .Where(c => !nameColumns.Contains(c.ColumnName)).ToList();
removeColumns.ForEach(c => tblNames.Columns.Remove(c));
foreach (var x in distinctNames)
    tblNames.Rows.Add(x.Name, x.Family, x.ID);

string[] propertyColumns = { "ID", "PropertyID", "PropertyEnergy" };
DataTable tblProperties = table.Clone();
removeColumns = tblProperties.Columns.Cast<DataColumn>()
    .Where(c => !propertyColumns.Contains(c.ColumnName)).ToList();
removeColumns.ForEach(c => tblProperties.Columns.Remove(c));
foreach (var x in distinctProperties)
    tblProperties.Rows.Add(x.ID, x.PropertyID, x.PropertyEnergy);

edited Nov 27 '14 at 10:17

answered Nov 27 '14 at 10:09

Tim Schmelter

450,073
74
686
939

Wouldn´t this also require an `EqualityComprarer` within `Distinct` to ignore the multiples? – MakePeaceGreatAgain Nov 27 '14 at 10:17
@HimBromBeere: no, because anonymous types have it already built-in. The `Equals` and `GetHashCode` methods on anonymous types are defined in terms of the `Equals` and `GetHashCode` methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal. Note that the order matters. – Tim Schmelter Nov 27 '14 at 10:20
@TimSchmelter Thank you for your great help..Is table my datatable?I used it but it says does not contain defintion for it..What should I write instead var in code? – Majid Hojati Nov 27 '14 at 12:26
@MajidHojati: yes, table is your table :) – Tim Schmelter Nov 27 '14 at 12:28
@TimSchmelter So why I get this error Error 20 'System.Data.EnumerableRowCollection' does not contain a definition for 'Distinct' and no extension method 'Distinct' accepting a first argument of type 'System.Data.EnumerableRowCollection' could be found (are you missing a using directive or an assembly reference?) – Majid Hojati Nov 27 '14 at 12:37
@MajidHojati: have you added `using System.Linq;` at the top of the file? – Tim Schmelter Nov 27 '14 at 12:40
@TimSchmelter Thanks..It was my mistake,Sorry.it fixed,But Why distinctNames does not have anything when I run it,It is empty,, – Majid Hojati Nov 27 '14 at 13:47
@MajidHojati: how have you checked it? Have you used the code below to create the datatable or do you have checked `distinctNames` in the debugger? User `distinctNames.ToList()` if you want to create a list of this anonymous type. It's impossible that it's empty if the DataTable contains rows. – Tim Schmelter Nov 27 '14 at 13:50
@TimSchmelter Thank you for your help,It is great..I managed to find the problem..It was about casting columns to wrong type – Majid Hojati Nov 27 '14 at 20:12

using distinct in DataTable.Select function

1 Answers1