Group a Datatable and copy to another table C#

Question

There is already a question here "https://stackoverflow.com/questions/27121248/report-generation-in-asp-net". I have same scenario where i have stocked on a specific point.

Please Help.

My DataTable has three columns

Name  Plot   Area
a     12     .90
a     13     .30
b     12     .45
b     13     .46
c     13     .98

I want my result to be

Name  Plot  Area
a     12    .90
      13    .30
b     12    .45
      13    .46
c     13    .98

And this is what i have tried . I have grouped this to linq and need to copy to another table. How can i achieve this :

static class Program
{
    static void Main(string[] args)
    {
        try
        {

            DataTable dtMain = new DataTable();
            dtMain.Columns.Add("Name", typeof(string));
            dtMain.Columns.Add("Plot", typeof(int));
            dtMain.Columns.Add("Area", typeof(decimal));

            DataRow dr1 = dtMain.NewRow();
            dr1["Name"] = "a";
            dr1["Plot"] = 12;
            dr1["Area"] = .90;
            dtMain.Rows.Add(dr1);

            DataRow dr2 = dtMain.NewRow();
            dr2["Name"] = "a";
            dr2["Plot"] = 13;
            dr2["Area"] = .30;
            dtMain.Rows.Add(dr2);

            DataRow dr3 = dtMain.NewRow();
            dr3["Name"] = "b";
            dr3["Plot"] = 12;
            dr3["Area"] = .45;
            dtMain.Rows.Add(dr3);
            DataRow dr4 = dtMain.NewRow();
            dr4["Name"] = "b";
            dr4["Plot"] = 13;
            dr4["Area"] = .46;
            dtMain.Rows.Add(dr4);
            DataRow dr5 = dtMain.NewRow();
            dr5["Name"] = "c";
            dr5["Plot"] = 13;
            dr5["Area"] = .98;
            dtMain.Rows.Add(dr5);

            DataTable dtFinal = dtMain.Clone();

            var groupedData = (from b in dtMain.AsEnumerable()
                               group b by b.Field<string>("Name") into g
                               select new
                              {
                                  Name = g.Key,
                                  Plot = g.Select(x => x.Field<int>("Plot")),
                                  Area = g.Select(x => x.Field<Decimal>("Area")),
                              }).ToList();

      //Next what should i do here.CopyToDataTable() no longer getting the correct data.
       dtFinal = groupedData.ToList().CopyToDataTable();

            Console.ReadLine();
        }
        catch (Exception objEx)
        {
            Console.WriteLine(objEx);
        }
    }


}

Answer 1

Don't be afraid to use loops ;)

var nameGroups = dtMain.AsEnumerable().GroupBy(row => row.Field<string>("Name"));

foreach (var nameGroup in nameGroups)
{
    bool isFirstRow = true;
    foreach (DataRow row in nameGroup)
    {
        DataRow newRow = dtFinal.Rows.Add();
        newRow.SetField("Name", isFirstRow ? nameGroup.Key : "");
        newRow.SetField("Plot", row.Field<int>("Plot"));
        newRow.SetField("Area", row.Field<decimal>("Area"));
        isFirstRow = false;
    }              
}

LINQ is not supposed to cause side-effects. So use it to select what you need to create the table.

Result:

a      12     0.9
       13     0.3
b      12     0.45
       13     0.46
c      13     0.98

If you insist on avoiding loops, here is the reason why you should use them. Look at this ugly, inefficient and strange query:

DataTable dtFinal = dtMain.Clone();
dtFinal = dtMain.AsEnumerable()
    .GroupBy(row => row.Field<string>("Name"))
    .SelectMany(grp => grp.Select((row, index)=> index == 0
        ? dtFinal.LoadDataRow(new object[]{ grp.Key, row.Field<int>("Plot"), row.Field<decimal>("Area")}, true)
        : dtFinal.LoadDataRow(new object[]{ "", row.Field<int>("Plot"), row.Field<decimal>("Area")}, true)))
    .CopyToDataTable();