Linq-to-sql Delete a record with FK constraints

Is it possible to SubmitChanges and ignore deletes that cause Foreign Key constraint errors?

Say I have object A and B. B's Primary Key is a Foreign Key in A. Now I change a record A in which I replace B for another B and delete the original B. An error will occur when the deleted B is referenced in another record A (or other tables containing B as Foreign Key). Is it possible to ignore the error and let the changes be made in the database, witho开发者_如何学运维ut deleting the old B?

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
db.Bs.DeleteOnSubmit(rec_b_old);
rec_a.B = some_other_b;
db.SubmitChanges();

---

Make two calls to SubmitChanges():

A rec_a = (from a in db.As where a.id == some_id).First();
B rec_b_old = rec_a.B;
rec_a.B = null;
rec_a.B = some_other_b;
db.SubmitChanges();
db.Bs.DeleteOnSubmit(rec_b_old);
try
{
    db.SubmitChanges();
}
catch(SqlException) { } // Ignore failed delete.

The second one might fail and in that case just ignore it. It is possible to try to submit everything, dig out the failing update/delete, remove it from the pending list and retry. However it requires far more code so I don't think it's worth to do it here.

Another solution is to put a trigger on the A table that deletes the B record if it is orphaned.

---

LINQ to SQL does not support cascading delete operations. You will need to either tell the database itself to do that or delete the child rows yourself first.

See Insert, Update, and Delete Operations (LINQ to SQL) for a detailed explanation.