How to use group by in Linq to process DataTable

Question

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 and 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<string>(DataRow, DataColumn)' requires a receiver of type" 

Answer 1

For example:

var results = from p in dt.AsEnumerable( )
              group p by p.Field<string>( "Productid" ) into g
              select new
              {
                  ProductId = g.Key,
                  CustomerId = string.Join( ";", from i in g select i.Field<string>( "CustomerID" ) ),
                  CustomerState = string.Join( ";", from i in g select i.Field<string>( "CustomerState" ) ),
                  CustomerCity = string.Join( ";", from i in g select i.Field<string>( "CustomerCity" ) ),
              };