How to process a DataRow with C# Linq to group and process data

June 16, 2023

I am getting a DataTable from sql server using a stored proc. The following shows a simplified verion of the data

        DataTable dt = new DataTable("Products");
        dt.Columns.Add("ProductID");
        dt.Columns.Add("CustomerID");
        dt.Columns.Add("CustomerState");
        dt.Columns.Add("CustomerCity");

        //DataRow row1 = table1.NewRow();
        object[] o = { 1, 1, "TX", "Austin" };
        dt.Rows.Add(o);

        object[] o1 = { 2, 2, "AZ", "Phoenix" };
        dt.Rows.Add(o1);

        object[] o2 = { 2, 3, "OK", "Oklahoma" };
        dt.Rows.Add(o2);

        object[] o3 = { 2, 4, "CO", "Denver" };
        dt.Rows.Add(o3);

        object[] o4 = { 3, 1, "TX", "Austin" };
        dt.Rows.Add(o4);

I am planning to group the data by ProductID. After grouping there should be only one row for each ProductID. If there are multiple ProductIDs then the others fields should be concatenated delimited by a semicolon as shown below.

ProductID   CustomerID      CustomerState   CustomerCity
1           1               TX              Austin
2           2;3;4           AZ;OK;CO        Phoenix;Oklahoma;Denver
3           1               TX              Austin

I think this can’t be done by ADO.Net. So I started to use LINQ as shown below. However

DataTable dt = ProcessData();//Data is from the above code where the DataTable is created and populated manually
        var results = from p in dt.AsEnumerable()
                      group p by p.Field<string>("Productid") into g
                      select new
                      {
                          productid = g.Key,
                          items = g.ToList(),
                          //customerid = g.Field<string>("customerid"),
                          //customerstate = g.Field<string>("customerstate"),
                          //customercity = g.Field<string>("customercity")
                      };

I had to comment out three lines as I am getting the following error: "CS1929: ‘IGrouping<string, DataRow>’ doesn’t contain a definition for ‘Field’ and the best extension method overload ‘DataRowExtensions.Field(DataRow, DataColumn)’ requires a receiver of type"

>Solution :

You can use Below code to concatenation based on group in LINQ

var results = 
   from p in dt.AsEnumerable()
   group p by p.Field<string>("Productid") into g
   select new
    {
      productid = g.Key,
      customerid = string.Join(",", g.Select(i => i.Field<string>("customerid"))),
      customerstate = string.Join(",", g.Select(i => i.Field<string>("customerstate"))),
       customercity = string.Join(",", g.Select(i => i.Field<string>("customercity"))),
      
     };