CODEWITHSUNDEEP
c#datatable
Majid Hojati
Majid Hojati

Reputation: 1790

using distinct in DataTable.Select function

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

Upvotes: 0

Views: 459

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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

Upvotes: 1

Related Questions