Linq-to-sql Delete a record with FK constraints

Question

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, without 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();

You might want to look into Cascading Deletes: http://stackoverflow.com/questions/4994587/sql-server-2005-cascading-delete — Joe, Sep 22 '11 at 19:30
if the old B is still being referenced by either records in A or some other table, isn't deleting that row of B the wrong thing to do? Why would you want to delete a row that's still being referenced? If you need to allow that, remove the FK constraint, since the FK is there to intentionally prevent what you want to do. — James Manning, Sep 26 '11 at 04:42
BTW, if the intent is "point the A record at the new B, and then delete the old B if it's not being referenced anymore", then I would do the rec_a.B change and submit that and then either do the delete of B as a best-effort change on its own (which you would have to back out on failure) or just look at its navigation properties to try and figure out if it's still used. It might help to explain a little bit as to why the 'B' record is being deleted here, though. :) — James Manning, Sep 26 '11 at 04:45

score 4 · Accepted Answer · answered Sep 22 '11 at 19:27

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.

This is exactly how I solved. A fellow developer pointed me in this direction. Thx anyways! — Roland Deschain, Sep 27 '11 at 07:32

score 0 · Answer 2 · edited Sep 22 '11 at 19:26

0

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.

edited Sep 22 '11 at 19:26

answered Sep 22 '11 at 19:25

Steve Danner

21,818
7
41
51

Linq-to-sql Delete a record with FK constraints

2 Answers2