Is this a bug in DataTable API? Changes are stored/executed in the "wrong sequence"

Question

Edit: any comments whether you think this is a .NET bug or not would be appreciated.

I have a bug which I've managed to simplify to the following scenario:

I have a DataTable where the primary keys must be kept consecutive, e.g. if you insert a row between other rows, you must first increment the ID of the succeeding rows to make space, and then insert the row.

And if you delete a row, you must decrement the ID of any succeeding rows to fill the gap left by the row in the table.

Test case that works correctly

Start with 3 rows in the table, with IDs 1, 2 and 3.

Then delete ID=2, and set ID=2 where ID=3 (to fill the gap); this works correctly. The dataTable.GetChanges() contains the deleted row, and then the modified row; when you run dataAdapter.Update(table) it executes fine.

Test case that does not work

However, if you start with 2 rows (IDs 1 and 2), then set ID=3 where ID=2, and insert ID=2, then commit (or accept) changes. This should be now be the same state as the first test.

Then you do the same steps as before, i.e. delete ID=2 and set ID=2 where ID=3, but now the dataTable.GetChanges() are in the wrong order. The first row is a modified row, and the second row is the deleted row. Then if you try dataAdapter.Update(table) it will give a primary key violation - it tried to modify a row to an already existing row before it deletes.

Workaround

I can think of a workaround to the problem, i.e. force it so that deleted rows are committed first, and then modified rows, and then added rows. But why is this happening? Is there another solution?

I think I have seen a similar "problem" before with dictionaries, that if you add some items, delete then, re-insert them, then they will not be in the same sequence that you added them (when you enumerate the dictionary).

Here are two NUnit tests which show the problem:

[Test]
public void GetChanges_Working()
{
    // Setup ID table with three rows, ID=1, ID=2, ID=3
    DataTable idTable = new DataTable();
    idTable.Columns.Add("ID", typeof(int));

    idTable.PrimaryKey = new DataColumn[] { idTable.Columns["ID"] };

    idTable.Rows.Add(1);
    idTable.Rows.Add(2);
    idTable.Rows.Add(3);

    idTable.AcceptChanges();

    // Delete ID=2, and move old ID=3 to ID=2
    idTable.Select("ID = 2")[0].Delete();
    idTable.Select("ID = 3")[0]["ID"] = 2;

    // Debug GetChanges
    foreach (DataRow row in idTable.GetChanges().Rows)
    {
        if (row.RowState == DataRowState.Deleted)
            Console.WriteLine("Deleted: {0}", row["ID", DataRowVersion.Original]);
        else
            Console.WriteLine("Modified: {0} = {1}", row["ID", DataRowVersion.Original], row["ID", DataRowVersion.Current]);
    }

    // Check GetChanges
    Assert.AreEqual(DataRowState.Deleted, idTable.GetChanges().Rows[0].RowState, "1st row in GetChanges should be deleted row");
    Assert.AreEqual(DataRowState.Modified, idTable.GetChanges().Rows[1].RowState, "2nd row in GetChanges should be modified row");
}

Output:

Deleted: 2
Modified: 3 = 2

1 passed, 0 failed, 0 skipped, took 4.27 seconds (NUnit 2.4).

Next test:

[Test]
public void GetChanges_NotWorking()
{
    // Setup ID table with two rows, ID=1, ID=2
    DataTable idTable = new DataTable();
    idTable.Columns.Add("ID", typeof(int));

    idTable.PrimaryKey = new DataColumn[] { idTable.Columns["ID"] };

    idTable.Rows.Add(1);
    idTable.Rows.Add(2);

    idTable.AcceptChanges();

    // Move old ID=2 to ID=3, and add ID=2
    idTable.Select("ID = 2")[0]["ID"] = 3;
    idTable.Rows.Add(2);

    idTable.AcceptChanges();

    // Delete ID=2, and move old ID=3 to ID=2
    idTable.Select("ID = 2")[0].Delete();
    idTable.Select("ID = 3")[0]["ID"] = 2;

    // Debug GetChanges
    foreach (DataRow row in idTable.GetChanges().Rows)
    {
        if (row.RowState == DataRowState.Deleted)
            Console.WriteLine("Deleted: {0}", row["ID", DataRowVersion.Original]);
        else
            Console.WriteLine("Modified: {0} = {1}", row["ID", DataRowVersion.Original], row["ID", DataRowVersion.Current]);
    }

    // Check GetChanges
    Assert.AreEqual(DataRowState.Deleted, idTable.GetChanges().Rows[0].RowState, "1st row in GetChanges should be deleted row");
    Assert.AreEqual(DataRowState.Modified, idTable.GetChanges().Rows[1].RowState, "2nd row in GetChanges should be modified row");
}

Output:

Modified: 3 = 2
Deleted: 2
TestCase 'GetChanges_NotWorking'
failed: 
  1st row in GetChanges should be deleted row
  Expected: Deleted
  But was:  Modified

Answer 1

It's not a bug, the point is that you are using ID's in a (very) nonstandard way. Two answers:

1) Use DataTable.GetChanges(DataRowState.Modified) to process your updates in order (I think it would be deleted, modified, inserted). This is what you have to do with Master/Detail relations as well (before .net 3.0)

2) Rethink your design, in general ID's should be immutable and allow for gaps etc. This will make all your database operations much more reliable and much easier. You can use another column to maintain a sequential numbering to present to the user.