Delete rows from a data table that exists in another data table

Question

I have a DataTable dt1 with columns C1(PK), C2, and C3(Unique Value) in a DataSet, on which after deleting some rows I will be doing AcceptChanges. I have another DataTable dt2 which is identical in structure to dtA. I would like to delete rows in dtA which exists in dtB compared in terms of C3(unique value) and do the AcceptChanges.

I could achieve very close, but not what I want by doing Except like below (learned from here):

var a = dt1.AsEnumerable().Select(r => r.Field<Guid>("C3"));
var b = dt2.AsEnumerable().Select(r => r.Field<Guid>("C3"));
var c = a.Except(b);

Problem with this approach is, I am getting only a list of values that belong in C3 column. I could do some more as shown here, but then again I will have a new DataTable, I wont still change the existing dt1 table.

Second approach I thought of was doing something like below:

foreach (DataRow r1 in ds1.Tables[0].Rows)
{
    foreach(DataRow r2 in dt2.Rows)
    {
        if (r1.Field<Guid>("Guid1") == r2.Field<Guid>("Guid2"))
        {
            r1.Delete();
        }
    }
}

Here as soon as row is deleted, I get an error saying "This row has been removed from a table and does not have any data." What am I missing here?

I also have a feeling that there might be a better way of doing this, just don't know lol.

I think that would be even better. I didn't think of that. If I can mark them as deleted, AcceptChange will take care of deleting job isn't it? — haku, Feb 09 '14 at 22:45
@SergeyBerezovskiy - I tried setting up the RowState, but it threw compile time error saying, it is a read only property. — haku, Feb 09 '14 at 22:47

Sergey Berezovskiy · Accepted Answer · 2014-02-09T23:08:17.697

10

I get an error saying 'This row has been removed from a table and does not have any data' What am I missing here?

You have two loops - outer and inner. And you have two problems here. First one is deleting row when match found. On next iteration of inner loop you are trying to get value of deleted row, which throws exception. But even if you'll break inner loop after deleting row, you'll have another problem - modification of rows you are enumerating. So, your code should look like:

foreach (DataRow r1 in dt1.Rows.Cast<DataRow>().ToArray()) // save rows to array
{
    foreach (DataRow r2 in dt2.Rows)
    {
        if (r1.Field<Guid>("C3") == r2.Field<Guid>("C3"))
        {
            r1.Delete();
            break; // break inner loop
        }
    }
}

But I'd go with LINQ approach - get rows for deletion by joining rows on id field, and then delete them from table:

var rowsToDelete = from r1 in dt1.AsEnumerable()
                   join r2 in dt2.AsEnumerable()
                        on r1.Field<Guid>("C3") equals r2.Field<Guid>("C3")
                   select r1;

foreach(DataRow row in rowsToDelete.ToArray())
   row.Delete(); // marks row as deleted;

NOTE: After you use Delete method on existing DataRow, its RowState becomes Deleted. Row remains Deleted until you call AcceptChanges, which removes row from table.

edited Feb 09 '14 at 23:08

answered Feb 09 '14 at 22:45

Sergey Berezovskiy

232,247
41
429
459

I think this works, but I do not get how is this working. I thought after executing the first part of the snippet, rowsToDelete would have a separate copy of r1, rather than pointing to the r1 itself. Looks like I was wrong with my understanding. – haku Feb 09 '14 at 22:59
But how come marking the rows as deleted in rowsToDelete not in dt1 make those rows available for delete while doing AcceptChanges on the dataset. Isn't rowsToDelete a different copy than dt1 after that linq code. Do I make sense at all haha? – haku Feb 09 '14 at 23:05
@TimSchmelter MSDN says that rows will be removed after accepting changes. See second paragraph in remarks of Delete() method – Sergey Berezovskiy Feb 09 '14 at 23:10
@HakuKalay nope, rowsToDelete is not a copy of rows - this is a references to rows which you have in dt1 – Sergey Berezovskiy Feb 09 '14 at 23:16
1

@SergeyBerezovskiy - Oh okay and thanks. So if were to only select column C1 and C3 from the dt1, would rowToDelete still reference to the respective rows in dt1? – haku Feb 09 '14 at 23:23
1

@HakuKalay nope, if you'll do projection (i.e. select only columns C1 and C3) then you will have anonymous object which is not related to any DataRow. That will be only value of fields – Sergey Berezovskiy Feb 09 '14 at 23:27

Delete rows from a data table that exists in another data table

1 Answers1