在 DataTable.Select 函数中使用 distinct 函数
本文关键字:函数 distinct Select DataTable | 更新日期: 2023-09-27 18:32:16
我有一个数据表,我想用这个数据表填充两个额外的数据表,这是我的表的简单形式
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
我想在数据表中使用此查询select distinct [name][family][id] from table
结果
John smith 1
Hannah smith 2
我再次在另一个数据表中使用此查询select [id][propertyid][propertyEnergy] from table
结果
1 12 Gas
1 13 Gas
1 14 null
1 15 Gas
2 16 Gas
2 17 Gas
2 18 Gas
我搜索了一下,发现我可以DataTable.Select
但是我看到的例子表明,我只能在DataTable.Select
中添加"在哪里",我不知道如何在其中执行诸如Distinct
之类的事情,你能帮帮我或给我一些提示怎么做吗?非常感谢
我会改用Linq-To-DataTable
:
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();
如果您需要两个额外的DataTables
则必须手动创建和填充它们,因为这些列与主表不同。您可以从上面的查询中循环填充它们。
这应该按原样工作:
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);