CODEWITHSUNDEEP
c#.netlinqlinq-to-sql
Roland Deschain
Roland Deschain

Reputation: 709

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

Upvotes: 1

Views: 5007

Answers (2)

Anders Abel
Anders Abel

Reputation: 69270

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.

Upvotes: 4

Steve Danner
Steve Danner

Reputation: 22158

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.

Upvotes: 0

Related Questions