DeleteOnSubmit does not observe the order between parent and children

Question

Hi everyone,

I find it very curious that DeleteOnSubmit doesn't execute the DELETE statements with respect to parent-child relationship. So if I have a table "parent" and a table "child" in a OneToMany relationship, and I do:

dataContext.DeleteOnSubmit(child1);
dataContext.DeleteOnSubmit(child2);
dataContext.DeleteOnSubmit(parent);
dataContext.SubmitChanges();

Linq2Sql will generate "DELETE parent" first, then "DELETE child" - which obviously fails due to the FK constraint. Even if I call DeleteOnSubmit(parent) as the first statement, then DeleteOnSubmit for children, the situation is the same: "DELETE parent" is the first statement executed.

The .dbml model shows the FK between tables. It appears that the only solution is to call [SubmitChanges] after DeleteOnSubmit for children (with the downside of unnecessary round-trips to DB).

Is this a bug in Linq2Sql? Is there another way to enforce the order? I'd expect Linq2Sql to do this properly as it has all information it needs (i.e. the FK) to execute the statements in the proper order.

Answer 1

Apparently it looks like Linq2Sql executes the SQL statements in the alphabetical order of the table names :) as probably the tables are hold internally in a sorted collection probably. It is the only way I can explain the behaviour: I have another set of tables, in which child table names are alphabetically before the parent table name, and (to my surprise) the statements are executed in the correct order, children first then parent.

Is there a way I could override this behaviour?

Answer 2

Just checked a similar case I had and what you see is what I saw as well.  I had to do a submitchanges after doing the DeleteOnSubmit for each child.

Have comments in the code indicating the "error" you see.  As for unnecessary round trips, the code generated for the "extra" submitchanges does not change anything.  There will be one delete for each child and the the second submitchanges will create a delete for the parent.

LS

---

Lloyd Sheen

Answer 3

Thanks for reply Lloyd, there doesn't appear to be any other way to have submitchanges behave. The extra submitchanges are not a big deal (at least in this case), as deletes are not really so frequent.

It is just a really bad oversight from Linq2Sql :(

Answer 4

Hi,

I am not able to reproduce your problem. On my side, the parent class is named "AParent", and the child class is named "Child".  Here is my code:

                List<Child> ps = context.Childs.Where(p => p.ParentId == 1).ToList();
                foreach (Child item in ps)
                {
                    context.Childs.DeleteOnSubmit(item);
                }
                context.AParents.DeleteOnSubmit(context.AParents.Where(p => p.Id == 1).First());
                context.SubmitChanges();