ConstraintException in System.Data.DataSet.Merge when deleting and adding a row with the same primary key

Question

We've got a CRUD app using System.Data.DataSet functionality. We recently found a bug where we're getting a System.Data.ConstraintException throwed when trying to use the System.Data.DataSet.Merge method in a specific case: when the user removes a row, then manually enters a new one with the same primary key column values.

Our expectation would be that—given that the row that was initially in the DataSet no longer exists—the new one should not trigger a primary key violation. But it seems it does.

Why do we get this exception and how to avoid it?

Minimal reproducible example:

// Initial DataSet state: One table with one column and one row. This code simply reproduces
// what would be fetched from the database.
var ds = new System.Data.DataSet("ds");
var t = new System.Data.DataTable("t");
var c1 = new System.Data.DataColumn("c1", System.Type.GetType("System.Int32"), "");
var c2 = new System.Data.DataColumn("c2", System.Type.GetType("System.Int32"), "");
t.Columns.Add(c1);
t.Columns.Add(c2);
t.PrimaryKey = new[] { c1 };
var r1 = t.NewRow();
r1["c1"] = 42;
r1["c2"] = 7;
t.Rows.Add(r1);
ds.Tables.Add(t);
ds.AcceptChanges();

// User removes a row, then adds a new one after a while. As it happens, they put the same
// value into the column here.
r1.Delete();

var r2 = t.NewRow();
r2["c1"] = 42;
r2["c2"] = 9000;
t.Rows.Add(r2);

// We try to merge the changes in:
var changes = ds.GetChanges();
foreach(System.Data.DataRow row in changes.Tables["t"].Rows)
{
    if(row.RowState != System.Data.DataRowState.Deleted)
    {
        row["c2"] = Math.Min((int)row["c2"], 10);  // Some example operation on changed rows.
    }
}
ds.Merge(changes);

Note: I've asked this question on StackOverflow, but maybe this place is better for this kind of question?

Edit: example updated to show an example of modifications applied to changes .

Answer 1

It seems to work if you execute:

ds.Merge( changes, preserveChanges: true );
ds.AcceptChanges( ); // (can be postponed)

Is this suitable?

Answer 2

Hi TomaszMelcer-1166,
Based on your code, the problem is on the primary key.
You merge with the same primary key and you can set DataSet.EnforceConstraints to false to fix it.

ds.EnforceConstraints = false;  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.