Combining Datatable duplicated items with a dictio

Question

I did have another question in which I thought I was well on my way only to realize that the Dictionary is of great use, but I need to be able to really use my Datatable to loop through.

The Dictionary is correctly showing the data, but my loop over the edited datatable is clearly showing both the edited and old data

https://dotnetfiddle.net/6BzsYh

    DataTable table1 = new DataTable();
    table1.Columns.Add("ID", typeof (int));
    table1.Columns.Add("Weight", typeof (int));
    table1.Columns.Add("Name", typeof (string));
    table1.Columns.Add("Breed", typeof (string));
    table1.Rows.Add(23, 57, "Koko", string.Empty);
    table1.Rows.Add(44, 130, "Fido", null);
    table1.Rows.Add(54, 130, "Jack", null);
    table1.Rows.Add(44, 130, "Thad", null);
    table1.Rows.Add(64, 130, "John", null);
    table1.Rows.Add(23, 130, "Brian", null);
    table1.Rows.Add(445, 130, "James", null);
    table1.Rows.Add(64, 134, "Adam", null);

    Dictionary<int, string> dict = new Dictionary<int, string>();

    for (int i = 0; i < table1.Rows.Count; i++)
    {
        int id = Convert.ToInt32(table1.Rows[i][0]);
        if (dict.ContainsKey(id))
        {
            //comma separate the Names
            dict[id] += ", " + table1.Rows[i][2].ToString();
            // change the Name value in the table
            table1.Rows[i][2] = dict[id];
        //Console.WriteLine(dict[id]);
        }
        else
        {
            dict.Add(id, table1.Rows[i][2].ToString());
        }
    //Console.WriteLine()
    }

    foreach (DataRow eaRow in table1.Rows)
    {
        Console.WriteLine("ID=" + eaRow[0] + "  Name=" + eaRow[2]);
    }

    Console.WriteLine("\n\n");
    //dictionary is correct
    foreach (var item in dict)
    {
        Console.WriteLine("ID=" + item.Key + " Name=" + item.Value);
    }

Answer 1

if you want a list of duplicate values you need to do something like the following I have not done tried this on a Range of Datatable fields so you would need to test this doing it with 2 different fields the ID field and Weight field

    DataTable table11 = new DataTable();
    table11.Columns.Add("ID", typeof(int));
    table11.Columns.Add("Weight", typeof(int));
    table11.Columns.Add("Name", typeof(string));
    table11.Columns.Add("Breed", typeof(string));
    table11.Rows.Add(23, 57, "Koko", string.Empty);
    table11.Rows.Add(44, 130, "Fido", null);
    table11.Rows.Add(54, 130, "Jack", null);
    table11.Rows.Add(44, 130, "Thad", null);
    table11.Rows.Add(64, 130, "John", null);
    table11.Rows.Add(23, 130, "Brian", null);
    table11.Rows.Add(445, 130, "James", null);
    table11.Rows.Add(64, 134, "Adam", null);

   var duplicate = table1.AsEnumerable()
       .Select(dr => dr.Field<int>("ID"))
       .GroupBy(x => x)
       .Where(g => g.Count() > 1)
       .Select(g => g.Key)
       .ToList();

   var duplicate2 = table1.AsEnumerable()
       .Select(dr => dr.Field<int>("Weight"))
       .GroupBy(x => x)
       .Where(g => g.Count() > 1)
       .Select(g => g.Key)
       .ToList();

if you want to remove duplicates from a datatable then refer to this stackoverflow posting

Best way to remove duplicate entries from a data table

Answer 2

Ok so do this then , note if you are wanting to preserve ALL the other columns values then you need to plumb those in , notice the stubbed one that I have in there that is commented out "other"

pros: complete control of your data

cons: not dynamic , and a bit of time to add all your columns

DataTable dtResult = table1.Clone(); // This will be empty at first

var distinctRows = table1.DefaultView.ToTable(true, "ID").Rows.OfType<DataRow>().Select(k => k[0] + "").ToArray();

       foreach (string id in distinctRows)
       {
           var rows = table1.Select("ID= '" + id + "'");
           string Name = "";
           //string other = "";
           foreach (DataRow row in rows)
           {

               Name+= row["Name"] + ",";
              // other += row["other"];
           }
           Name = Name.Trim(',');

           dtResult.Rows.Add(ID, Name)  // other);
           Name = "";
           //other = "";


       }

       //var output = dtResult; 



        foreach(DataRow dr in dtResult.Rows)
        {
            Console.WriteLine(dr[0] + " --- " + dr[1]); // + "--- " + dr[2]);
        }