DataTable将结果分组在一行 [英] DataTable group the result in one row
本文介绍了DataTable将结果分组在一行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在我的代码中，首先我将ID的值作为标题，然后组织每个ID的属性值。我想要的是使用其ID值对相同的名称，姓氏和注释进行分组。
我的第一张表格如下所示：
ID名称Lastmame评论属性
1 kiki ha hello FF
3 lola mi hi AA
2 ka xe什么UU
2 kiki ha hello SS
我使用我的代码后：
姓名姓氏评论1 3 2
kiki ha hello FF
lola mi hi AA
ka xe什么UU
kiki ha hello SS
我想要的是：
姓氏姓氏评论1 3 2
kiki ha hello FF SS
lola mi hi AA
ka xe什么UU
我的代码：
DataTable table1 = new DataTable （康德）;
table1.Columns.Add（Comment，typeof（String））;
table1.Columns.Add（Name，typeof（String））;
table1.Columns.Add（Lastname，typeof（String））;
DataTable comment = new DataTable（Comment）;
comment.Columns.Add（ID，typeof（String））;
comment.Columns.Add（Comment，typeof（String））;
comment.Columns.Add（Attribute，typeof（String））;
DataSet ds = new DataSet（DataSet）;
ds.Tables.Add（table1）;
ds.Tables.Add（comment）;
object [] o1 = {hello，kiki，ha};
object [] o2 = {lola，mi};
object [] o3 = {what，ka，xe};
object [] c1 = {1，hello，FF};
object [] c2 = {3，AA};
object [] c3 = {2，what，UU};
object [] c4 = {2，hello，SS};
table1.Rows.Add（o1）;
table1.Rows.Add（o2）;
table1.Rows.Add（o3）;
comment.Rows.Add（c1）;
comment.Rows.Add（c2）;
comment.Rows.Add（c3）;
comment.Rows.Add（c4）;
var results = from tb1 in comment.AsEnumerable（）
连接tb2在table1.AsEnumerable（）
在tb1.Field< string>（Comment）等于tb2。字段< string>（注释）
选择新
{
ID = tb1.Field< String>（ID），
Name = tb2.Field< String> （Name），
Lastname = tb2.Field< String>（Lastname），
注释= tb1.Field< String>（Comment），
Attribute = tb1。字段< String>（Attribute），
};
DataTable result = LINQToDataTable（results）;
var products = result.AsEnumerable（）
.GroupBy（c => c [ID]）
.Where（g =>！（g.Key是DBNull））
.Select（g =>（string）g.Key）
.ToList（）;
var newtable = result.Copy（）;
products.ForEach（p => newtable.Columns.Add（p，typeof（string）））;
foreach（newtable.AsEnumerable（）中的var row）
{
if（！（row [ID]是DBNull））row [（string）row [ ID]] = row [Attribute];
}
newtable.Columns.Remove（ID）;
newtable.Columns.Remove（Attribute）;
var result11 = from newtable.AsEnumerable（）
group t1 by new {Name = t1.Field< String>（Name），LastName = t1.Field< String> （LastName），Comment = t1.Field< String>（Comment），} into grp
select new
{
Name = grp.Key.Name，
LastName = grp.Key.LastName，
注释= grp.Key.Comment，
//这里的东西
};
使用System.Reflection;
public DataTable LINQToDataTable< T>（IEnumerable< T> varlist）
{
DataTable dtReturn = new DataTable（）;
//列名
PropertyInfo [] oProps = null;
if（varlist == null）return dtReturn;
foreach（T rec in varlist）
{
if（oProps == null）
{
oProps =（（Type）rec.GetType ））GetProperties（）;
foreach（oProps中的PropertyInfo pi）
{
键入colType = pi.PropertyType;
if（（colType.IsGenericType）&&（colType.GetGenericTypeDefinition（）
== typeof（Nullable<）））
{
colType = colType.GetGenericArguments（）[0];
}
dtReturn.Columns.Add（new DataColumn（pi.Name，colType））;
}
}
DataRow dr = dtReturn.NewRow（）;
foreach（oProps中的PropertyInfo pi）
{
dr [pi.Name] = pi.GetValue（rec，null）== null？ DBNull.Value：pi.GetValue
（rec，null）;
}
dtReturn.Rows.Add（dr）;
}
return dtReturn;
}
解决方案
根据<一个href =http://stackoverflow.com/a/12914411/390819>另一个答案：
一种方法是将所有结构中的变量列（如字典）
为此，请使用以下查询：
var variableColumnNames = newtable.Columns.Cast< DataColumn>（）
.Select（c => c.ColumnName）
.Except（new [] { 名字，姓氏，评论））;
var result11 = from newtable.AsEnumerable（）
group t1 by new
{
Name = t1.Field< String>（Name），
LastName = t1.Field< String>（LastName），
注释= t1.Field< String>（Comment），
} into grp
select new
{
grp.Key.Name，
grp.Key.LastName，
grp.Key.Comment，
值= variableColumnNames.ToDictionary（
columnName => columnName，
columnName => grp.Max（r => r.Field< String>（columnName）））
};
如果您真的需要在类中具有可变数量的属性，那么这是不可能的如我所知，所以唯一可行的方法是将结果输出到另一个 DataTable （我们可以根据需要添加任意列）。
方法＃2 - 使用动态
LINQ查询：
var result11 = from t1 in newtable.AsEnumerable（）
group t1 by new
{
Name = t1.Field< String>（Name），
LastName = t1.Field< String>（LastName），
注释= ），
} into grp
选择CreateNewDynamicObject
（
grp.Key.Name，
grp.Key.LastName，
grp.Key.Comment，
variableColumnNames.ToDictionary（
columnName => columnName，
columnName => grp.Max（r => r。字段< String>（columnName）））
）;
}
创建动态对象的新方法：
私有静态动态CreateNewDynamicObject（
string name，string lastName，string comment，Dictionary< string，string> customProperties）
{
dynamic obj = new ExpandoObject（）;
obj.Name = name;
obj.LastName = lastName;
obj.Comment = comment;
foreach（customProperties中的var prop）
（obj as IDictionary< string，Object>）。Add（prop.Key，prop.Value ??）;
return obj;
}
方法＃3 - 输出到 DataTable
生成的 DataTable （ destinationTable ）可以用作 DataGridView 的源代码：
var destinationTable = new DataTable（）;
foreach（newtable.Columns.Cast< DataColumn>（）中的var列）
destinationTable.Columns.Add（column.ColumnName，typeof（String））;
var result11 =
from newtable.AsEnumerable（）
group t1 by new
{
Name = t1.Field< String>（名称），
LastName = t1.Field< String>（Lastname），
注释= t1.Field< String>（Comment），
}
into grp
选择
variableColumnNames.ToDictionary（
columnName => columnName，
columnName => grp.Max（r => r.Field< String>（columnName）） ）
.Concat（新字典< string，string>
{
{Name，grp.Key.Name}，
{Lastname，grp.Key.LastName }，
{评论，grp.Key.Comment}
}
）.ToDictionary（x => x.Key，x => x.Value）;
foreach（result11中的var row）
{
var newRow = destinationTable.NewRow（）;
foreach（vartableName in newtable.Columns.Cast< DataColumn>（）。select（c => c.ColumnName））
newRow [columnName] = row [columnName];
destinationTable.Rows.Add（newRow）;
}
I have a DataTable and want to group Name, LastName and Comment. The rest should be in the same row. In my Code firstly i make ID's values as header and then organize the Attribute values to each ID. What I want here is to group the the same Name, Lastname and Comment with their ID values. My first Table looks like that:
ID Name Lastmame Comment Attribute
1 kiki ha hello FF
3 lola mi hi AA
2 ka xe what UU
2 kiki ha hello SS
After i use my code:
Name Lastname Comment 1 3 2
kiki ha hello FF
lola mi hi AA
ka xe what UU
kiki ha hello SS
what i want to have is:
Name Lastname Comment 1 3 2
kiki ha hello FF SS
lola mi hi AA
ka xe what UU
My Code:
DataTable table1 = new DataTable("Kunde");
table1.Columns.Add("Comment", typeof(String));
table1.Columns.Add("Name", typeof(String));
table1.Columns.Add("Lastname", typeof(String));
DataTable comment = new DataTable("Comment");
comment.Columns.Add("ID", typeof(String));
comment.Columns.Add("Comment", typeof(String));
comment.Columns.Add("Attribute", typeof(String));
DataSet ds = new DataSet("DataSet");
ds.Tables.Add(table1);
ds.Tables.Add(comment);
object[] o1 = { "hello", "kiki", "ha" };
object[] o2 = { "hi", "lola", "mi" };
object[] o3 = { "what", "ka", "xe" };
object[] c1 = { 1, "hello", "FF" };
object[] c2 = { 3, "hi", "AA" };
object[] c3 = { 2, "what", "UU" };
object[] c4 = { 2, "hello", "SS" };
table1.Rows.Add(o1);
table1.Rows.Add(o2);
table1.Rows.Add(o3);
comment.Rows.Add(c1);
comment.Rows.Add(c2);
comment.Rows.Add(c3);
comment.Rows.Add(c4);
var results = from tb1 in comment.AsEnumerable()
join tb2 in table1.AsEnumerable()
on tb1.Field<string>("Comment") equals tb2.Field<string>("Comment")
select new
{
ID = tb1.Field<String>("ID"),
Name = tb2.Field<String>("Name"),
Lastname = tb2.Field<String>("Lastname"),
Comment = tb1.Field<String>("Comment"),
Attribute = tb1.Field<String>("Attribute"),
};
DataTable result = LINQToDataTable(results);
var products = result.AsEnumerable()
.GroupBy(c => c["ID"])
.Where(g => !(g.Key is DBNull))
.Select(g => (string)g.Key)
.ToList();
var newtable = result.Copy();
products.ForEach(p => newtable.Columns.Add(p, typeof(string)));
foreach (var row in newtable.AsEnumerable())
{
if (!(row["ID"] is DBNull)) row[(string)row["ID"]] = row["Attribute"];
}
newtable.Columns.Remove("ID");
newtable.Columns.Remove("Attribute");
var result11 = from t1 in newtable.AsEnumerable()
group t1 by new { Name = t1.Field<String>("Name"), LastName = t1.Field<String>("LastName"), Comment = t1.Field<String>("Comment"), } into grp
select new
{
Name = grp.Key.Name,
LastName = grp.Key.LastName,
Comment = grp.Key.Comment,
//Something here
};
using System.Reflection;
public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
{
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}
解决方案
Based on the comments to this other answer:
One approach would be to stuff all the variable columns in a structure (like a dictionary).
In order to do this, use the following query:
var variableColumnNames = newtable.Columns.Cast<DataColumn>()
.Select(c => c.ColumnName)
.Except(new[]{"Name", "Lastname", "Comment"});
var result11 = from t1 in newtable.AsEnumerable()
group t1 by new
{
Name = t1.Field<String>("Name"),
LastName = t1.Field<String>("LastName"),
Comment = t1.Field<String>("Comment"),
} into grp
select new
{
grp.Key.Name,
grp.Key.LastName,
grp.Key.Comment,
Values = variableColumnNames.ToDictionary(
columnName => columnName,
columnName => grp.Max(r => r.Field<String>(columnName)))
};
If you really need to have a variable number of properties in the class, this is not possible as far as I know, so the only plausible way to do that is to output the result to another DataTable (to which we can add as many columns as we want).
Approach #2 - using dynamic
The LINQ query:
var result11 = from t1 in newtable.AsEnumerable()
group t1 by new
{
Name = t1.Field<String>("Name"),
LastName = t1.Field<String>("LastName"),
Comment = t1.Field<String>("Comment"),
} into grp
select CreateNewDynamicObject
(
grp.Key.Name,
grp.Key.LastName,
grp.Key.Comment,
variableColumnNames.ToDictionary(
columnName => columnName,
columnName => grp.Max(r => r.Field<String>(columnName)))
);
}
the new method that creates the dynamic object:
private static dynamic CreateNewDynamicObject(
string name, string lastName, string comment, Dictionary<string, string> customProperties)
{
dynamic obj = new ExpandoObject();
obj.Name = name;
obj.LastName = lastName;
obj.Comment = comment;
foreach (var prop in customProperties)
(obj as IDictionary<string, Object>).Add(prop.Key, prop.Value ?? "");
return obj;
}
Approach #3 - outputting to a DataTable
The resulting DataTable (destinationTable) can be used as a source for a DataGridView:
var destinationTable = new DataTable();
foreach (var column in newtable.Columns.Cast<DataColumn>())
destinationTable.Columns.Add(column.ColumnName, typeof(String));
var result11 =
from t1 in newtable.AsEnumerable()
group t1 by new
{
Name = t1.Field<String>("Name"),
LastName = t1.Field<String>("Lastname"),
Comment = t1.Field<String>("Comment"),
}
into grp
select
variableColumnNames.ToDictionary(
columnName => columnName,
columnName => grp.Max(r => r.Field<String>(columnName)))
.Concat(new Dictionary<string, string>
{
{"Name", grp.Key.Name},
{"Lastname", grp.Key.LastName},
{"Comment", grp.Key.Comment}
}
).ToDictionary(x => x.Key, x => x.Value);
foreach (var row in result11)
{
var newRow = destinationTable.NewRow();
foreach (var columnName in newtable.Columns.Cast<DataColumn>().Select(c => c.ColumnName))
newRow[columnName] = row[columnName];
destinationTable.Rows.Add(newRow);
}
这篇关于DataTable将结果分组在一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
