Combining Datatable duplicated items with a dictionary and getting issue with duplicate datatable data

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);
    }

Well, yeah. You're not doing anything to remove the duplicate rows from table1, so of course they're still there. — Dave, Jan 11 '16 at 21:21
How do I merge 2 rows ? I want to replace those existing rows by creating a single row — , Jan 11 '16 at 21:31
are you looking for a way to check for Duplicate values in a datatable..? if so there is a way to do this I can post an example for you to test and you can take it from there also you are asking a lot of questions like `How do I ` when you can google that — MethodMan, Jan 11 '16 at 21:32
What exactly is your end goal in this? TO have a DataTable with the "merged" data? — Dave, Jan 11 '16 at 21:35
End goal is to end up with being above to loop over datatable that combines duplicate rows with various specific columns but based on the ID ( real data is like 30 columns so I was trying to display with jsfiddle a representation ) — , Jan 11 '16 at 21:40

score 0 · Answer 1 · edited May 23 '17 at 11:44

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

Combining Datatable duplicated items with a dictionary and getting issue with duplicate datatable data

1 Answers1

Linked