DataTable linq查询按列分组 [英] DataTable linq query to group by a column
问题描述
我使用C#开发了一个Windows应用程序。使用DataTable列出所有信息。
我的结果:
主要负责人< 2Days< 5Days< 10Days< 20Days> 20Days Grand-Total
Dan Mahoney 1
Richard James Whitaker 1
Richard James Whitaker 1
Richard James Whitaker 1
理查德詹姆斯惠特克1
韦恩邓恩1
韦恩邓恩1
理查德詹姆斯惠特克1
韦恩邓恩1
理查德詹姆斯惠特克1
Richard James Whitaker 1
预期成果:
主要所有者< 2Days< 5Days< 10Days< 20Days> 20Days Grand-Total
Dan Mahoney 1
Richard James Whitaker 7
Wayne Dunne 3
我需要一个linq查询来达到预期的效果。
请帮助我解决这个问题。
在此先感谢。
如果您要创建新的 然后在源表的键列上使用 DataTable $包含汇总值的c $ c>并不是整个任务的最佳工具。您应该使用
DataTable.Clone
创建具有相同模式的空表。
Enumerable.GroupBy
。最后使用一个循环来添加 DataRow
s与汇总值:
var ownerGroups = dt.AsEnumerable()
.GroupBy(row => row.Field< string>(Lead Owners));
var dt2 = dt.Clone();
var intColumns = dt2.Columns.Cast< DataColumn>()
.Where(c => c.DataType == typeof(int))。ToArray();
foreach(ownerGroups中的var grp)
{
var row = dt2.Rows.Add();
row.SetField(Lead Owners,grp.Key);
foreach(DataColumn col in intColumns)
{
bool anyNonNull = grp.Any(r => r.Field< int?>(col.Ordinal).HasValue);
int? sum = anyNonNull? grp.Sum(r => r.Field< int?>(col.Ordinal)):null;
row.SetField(col,sum);
$ b我已经假定数字列的类型是 int
。
结果表:
Dan Mahoney null null 1
Richard James Whitaker null 7 null
Wayne Dunne 3 null null
I have developed a Windows application using C#. Used a DataTable to list all information. That Data-Table has duplicate values.
My result:
Lead Owners <2Days <5Days <10Days <20Days >20Days Grand-Total
Dan Mahoney 1
Richard James Whitaker 1
Richard James Whitaker 1
Richard James Whitaker 1
Richard James Whitaker 1
Wayne Dunne 1
Wayne Dunne 1
Richard James Whitaker 1
Wayne Dunne 1
Richard James Whitaker 1
Richard James Whitaker 1
Expected Result:
Lead Owners <2Days <5Days <10Days <20Days >20Days Grand-Total
Dan Mahoney 1
Richard James Whitaker 7
Wayne Dunne 3
I need a linq query to achieve the expected result.
Kindly help me to get this. Thanks in advance.
If you want to create a new DataTable
that contains the aggregated values LINQ is not the best tool for the whole task. You should use DataTable.Clone
to create an empty table with the same schema.
Then use can use Enumerable.GroupBy
on your source table's key-column. Finally use a loop to add the DataRow
s with the aggregated values:
var ownerGroups = dt.AsEnumerable()
.GroupBy(row => row.Field<string>("Lead Owners"));
var dt2 = dt.Clone();
var intColumns = dt2.Columns.Cast<DataColumn>()
.Where(c => c.DataType == typeof(int)).ToArray();
foreach (var grp in ownerGroups)
{
var row = dt2.Rows.Add();
row.SetField("Lead Owners", grp.Key);
foreach (DataColumn col in intColumns)
{
bool anyNonNull = grp.Any(r => r.Field<int?>(col.Ordinal).HasValue);
int? sum = anyNonNull ? grp.Sum(r => r.Field<int?>(col.Ordinal)) : null;
row.SetField(col, sum);
}
}
I have presumed that the type of the numeric columns is int
.
Resulting table:
Dan Mahoney null null 1
Richard James Whitaker null 7 null
Wayne Dunne 3 null null
这篇关于DataTable linq查询按列分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!