DataTable group the result in one row

Question

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

Answer 1

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