asked on

Linq: datatable queries

Given two datatables

Table1 (Id, etc)
Table2(id, prod, etc)

I want to merge the two table in such a way that will add a column to table1 "prod" that contains a comma delimited list of values from prod 2

While the code attach gets me the desired result.

Does anybody have a better suggestion on how to do this?

DataTable dtDealers = ds.Tables[DTDEALERLIST].Copy();
dtDealers.Columns.Add("Products", typeof (string));

var res = from d in ds.Tables[DTDEALERLIST].AsEnumerable()
                                  join p in ds.Tables[DTPRODUCTS].AsEnumerable() on d.Field<int>("dealerList_Id") equals p.Field<int>("dealerList_Id") into r
                                  from p in r.DefaultIfEmpty()
                                  select new
                                             {
                                                 ID = p.Field<int>("dealerList_Id"),
                                                 List = r.ToList().Aggregate(new StringBuilder(), (sb, l) => sb.Append(l.Field<string>("products_Text")).Append(","), sb =>
                                                                                                                                                                          {
                                                                                                                                                                              if (sb.Length > 0) sb.Length--;
                                                                                                                                                                              return sb.ToString();
                                                                                                                                                                          })
                                             };

                        var dis = res.Distinct();

                        foreach(var d in dis)
                        {
                            dtDealers.Rows[d.ID]["Products"] = d.List;
                        }

Open in new window