Compare and delete datatable row using C#:

Question

I have two tables such as DTTable1 and DTTable2. It has the following records.

DTTable1:

    ItemID     Specification    Amount
   ---------  ---------------  ---------
      1             A             10
      1             B             20
      1             C             30

DTTable1:

    ItemID     Specification    Amount
   ---------  ---------------  ---------
      1             A             10
      1             B             20
      1             C             30
      2             A             10
      2             B             20
      3             A             10
      3             B             20

Here I want to compare these two tables. If DTTable1 records present in DTTable2(consider only ItemID) then remove the corresponding rows which has the ItemID same as DTTable1.

I have tried foreach and forloop. Using ForEach:

   foreach (DataRow DR in DTTable2.Rows)
   {
      if (DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
      {
           DTTable2.Rows.Remove(DR);                            
      }
   }
   DTTable2.AcceptChanges();

It showed the error, "Collection was modified; enumeration operation might not execute". So I used For Loop, It also not given the desired result.

Using For Loop:

   for (int i = 0; i < DTTable2.Rows.Count; i++)
   {
       if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
       {
           DTTable2.Rows.RemoveAt(i);
       }
   }
   DTTable2.AcceptChanges();

But sometimes, the second row doesn't remove from the table. I get the final DataTable as

    ItemID     Specification    Amount
   ---------  ---------------  ---------
      1             B             20
      2             A             10
      2             B             20
      3             A             10
      3             B             20

How to solve this? What is the simplest method to do this?

Solution 1

You can't modify a collection while you are enumerating it as in your first example. You should instead get a list of the rows to delete and then delete them.

List<DataRow> rowsToDelete = new List<DataRow>();
foreach (DataRow DR in DTTable2.Rows)
{
    if (DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
        rowsToDelete.Add(DR);           
}

foreach (var r in rowsToDelete)
    DTTable2.Rows.Remove(r);

Or, inline using linq:

DTTable2.Rows
    .Where(DR => DR["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
    .ToList()
    .ForEach(r => DTTable2.Rows.Remove(r));

---

Your second example fails because once a row is removed, the indexes of subsequent rows change but you continue to increment i, which effectively skips over the row immediately following the deleted row. There are two ways around this:

for (int i = DTTable2.Rows.Count - 1; i >= 0; i--)
    if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
        DTTable2.Rows.RemoveAt(i);

Or

int i = 0;
while (i < DTTable2.Rows.Count)
{
    if (DTTable2.Rows[i]["ItemID"].ToString() == DTTable1.Rows[0]["ItemID"].ToString())
        DTTable2.Rows.RemoveAt(i);
    else
        i++;
}

---

Side note: I wonder if you really mean to always compare to row 0's data, given your description. Perhaps you meant to compare all rows like the following instead (although not optimal at all)?

if (DTTable1.Any(r => DTTable2.Rows[i]["ItemID"].ToString() == r["ItemID"].ToString()))

Solution 2

Try to use:

var list = DTTable2.Rows.ToList();//create new list of rows
foreach (DataRow DR in list)
{
    //if bla bla bla
    DTTable2.Rows.Remove(DR);
}

Solution 3

You can use LINQ to DataTable:

var result =  DTTable1.AsEnumerable()
                   .Where(row => !DTTable2.AsEnumerable()
                                         .Select(r => r.Field<int>("ItemID"))
                                         .Any(x => x == row.Field<int>("ItemID"))
                  ).CopyToDataTable();

Comments

[Murtuza Kabul]

Are you selecting these records from the database, if so, I can help you with the query which will select just the required records as it is far more efficient to get it done with sql