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
};
LINQToDataTable method definition
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).
dynamicThe 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;
}
DataTableThe 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);
}