Table doesn't have primary key c#

Question

Im trying to delete rows from DataTable AllItems with rows from DataTables Items; The purpose of this to get items from DataTable AllItems which are not inside DataTable Items

All these rows Fiiled from same Excel file which contains several columns and are equal.

I have tried using foreach loop:

foreach(DataRow dr in AllItems.Rows)
{
if (Items.Contains(dr))
{
AllItems.Rows.Remove(dr);
}

But I get following error: Table doesn't have primary key.

Does anyone knows how i can delete these rows?

Solution

You have a few choices here:

1. Add a Primary Key

you can add a primary key to your data table when creating it.

Assuming you had a column called "Id" then you would do it this way:

AllItems.PrimaryKey = new DataColumn[] { workTable.Columns["Id"] };} 

Or, for cases where your primary key is a composite key (multiple columns):

AllItems.PrimaryKey = new DataColumn[] { 
                             workTable.Columns["Id"], 
                             workTable.Columns["Name"] };} 

This would then allow Contains to work correctly.

2. Use a DataView

You can use a DataView to filter out the distinct rows;

DataView view = new DataView(AllItems);
DataTable distinctValues = view.ToTable(true, "Column1", "Column2" , ..., "ColumnN");

3. Find Matching Rows using Select

Or you can rely on the Select method to test if a corresponding row exists in the Items DataTable based on a statement that's like a SQL WHEREclause:

List<DataRow> rowsToRemove = new List<DataRow>();

foreach(DataRow allItemRow in AllItems.Rows)
{
    if(Items.Select(String.Format("Id = {0}"), 
          allItemRow.Field<Int32>("Id")).Length == 0)
    {
        rowsToRemove.Add(allItemRow);
    }
}

rowsToRemove.ForEach(x => x.Delete());

AllItems.AcceptChanges();

Note that it's important NOT to remove rows while you are iterating the collection of Rows in AllItems - instead, collect these rows, and remove them afterwards.

4. Filter on the way in

Also note, and I haven't tried it, but, depending on how you are selecting the rows out of Excel, you may be able to use the SQL DISTINCT clause; if you are using ODBC to load data from Excel then you could try filtering at source.