Delete rows in datatable from another datatable

Question

I have two DataTables: allRows and rowsToDelete. I want to delete rows from allRows that rowsToDelete contains. Both tables have the same structure (same columns and their datatypes), but I can't know exact column names and even their quantity.

object.Equals() method recognizes rows from different tables as not equal so I can't use this approach.

From googling and reading StackOverflow I got an idea that probably it can be even done in one line, but I don't know how to build condition for this case:

allRows = allRows.AsEnumerable().Where(???).CopyToDataTable();

score 0 · Answer 1 · answered Jul 19 '18 at 05:17

0

I don't know the structure of your data but generally speaking you can do

var cleanedUp = allRows.Where(row => !rowsToDelete.Any(row2 => row2.Id == row.Id ));

answered Jul 19 '18 at 05:17

tymtam

31,798
8
86
126

That's the problem - structure can be different and there can be no "Id" column – Gleb Glazyrin Jul 19 '18 at 05:22
https://stackoverflow.com/questions/4332635/c-sharp-compare-two-objects-of-unknown-types-including-reference-and-value-type – tymtam Jul 19 '18 at 05:28

score 0 · Answer 2 · answered Jul 19 '18 at 05:29

0

If you don not have any primary key then check multiple columns that combinations(composite key) provide you uniqueness on the behalf of which you can easily delete rows from all rows having similar data exist in rowsTodelete table.

answered Jul 19 '18 at 05:29

MuhammadWaseem

46
6

If you read the question, the user does not know about the columns and their types – Ipsit Gaur Jul 19 '18 at 05:31
but i think user have rights to access database from there by select query he can access columns name easily. – MuhammadWaseem Jul 19 '18 at 05:36
I would say that's not the way of approaching the solution – Ipsit Gaur Jul 19 '18 at 05:37
but for my opinion under this circumstance this is the appropriate way to do this if you delete data from single table with out any identity column then first you have to now at least column names . If table exist then you easily use select statement to find column names isn't it? – MuhammadWaseem Jul 19 '18 at 05:45

score 0 · Answer 3 · answered Jul 19 '18 at 05:34

0

OR

First implement inner join among two tables on similar column for retrieving data that you want to delete and insert that into any temp table and delete this data from allrows table.

Hope that will help you.

answered Jul 19 '18 at 05:34

MuhammadWaseem

46
6

Gleb Glazyrin · Accepted Answer · 2018-07-21T06:43:30.693

This solution works for any data structure, any columns' number and types. We present DataRows as arrays and compare each cell.

public static void DeleteCopies(DataTable allRows, DataTable rowsToDelete)
    {
        foreach (DataRow rowToDelete in rowsToDelete.Rows)
        {
            foreach (DataRow row in allRows.Rows)
            {
                var rowToDeleteArray = rowToDelete.ItemArray;
                var rowArray = row.ItemArray;
                bool equalRows = true;
                for (int i = 0; i < rowArray.Length; i++)
                {
                    if (!rowArray[i].Equals(rowToDeleteArray[i]))
                    {
                        equalRows = false;
                    }                            
                }
                if (equalRows)
                {
                    allRows.Rows.Remove(row);
                    break;
                }
            }
        }
    }

score 0 · Answer 5 · answered Jul 19 '18 at 07:21

0

Your task has no solution, and you change the conditions, first you write:

Both tables have the same structure (same columns and their datatypes)

then you write:

That's the problem - structure can be different and there can be no "Id" column

If you can even change the data, you can subtract both tables and investigate their structure, columns, etc. But you insist that you do not know them.

When you find out the structure, the task becomes elementary, smth like this(i use my structure from my own project):

var foo = DbContext
            .Set<Task>()
            .Select(x => new{x.Assignee, x.Availability})
            .ToList();

        var foo2 = DbContext
            .Set<Task2>()
            .Select(x => x)
            .ToList();

        var bar = foo2.Where(x => foo.Select(y => y.Assignee).Contains(x.Assignee) 
                                  && foo.Select(y => y.Availability).Contains(x.Availability));

        DbContext.RemoveRange(bar);
        DbContext.SaveChanges();

you can write more elegantly, but it's so obvious

answered Jul 19 '18 at 07:21

Eduard M

247
2
9

I mean that it can be different from time to time but anyway it would be the same for both tables – Gleb Glazyrin Jul 19 '18 at 13:37
I supposed you just tried to get some votes, not solve your problem. It's not real life application and your solution prove it: first of all - SequenceEqual (if you do not redefine them) compare reference, not data(and obviously you get false even data is equal), even it same type second, dude, really, you just take the table's structure and get it inline by linq, if you have access to DB, you can get the structure of the table. ох уж эти братья славяне... какой пассаж.;) – Eduard M Jul 19 '18 at 13:52
It's real life application that extracts DataTables from user's .dbf files - that's why its' structure is unknown – Gleb Glazyrin Jul 20 '18 at 14:16
@GlebGlazyrin https://stackoverflow.com/questions/14958098/why-sequenceequal-for-listt-returns-false – Eduard M Jul 20 '18 at 14:32
fixed this problem – Gleb Glazyrin Jul 21 '18 at 06:43

Delete rows in datatable from another datatable

5 Answers5