none
Order of execution for SQL DELETE statements RRS feed

  • Question

  • I have two tables with a one-to-many relationship. There is no on-delete-cascade on the relation, and it is not an option to enable this. When marking entities for deletion (both parent and children) then EF will issue the SQL DELETE statements in the wrong order, i.e. attempt to delete the parent first, resulting in a referential constraint violation.

    It does not matter whether I mark the parent or the children for deletion first, EF will always issue the DELETE statement for parent first (established using EFPRofiler).

    My findings this far is that there is no means of instructing EF in which order to execute SQL statements. The relationship is correctly defined in the EF model, with working navigation properties on the two entities.

    How does EF determine the SQL execution order, and what can be wrong with this simple scenario that causes EF to get it wrong?

    Thanks in advance for any input.

    Thursday, November 1, 2012 8:21 AM

All replies

  • Hi,

    Are you using Code first model approach or model first. I hope you have clearly specified the relationship between both the entities in the code (if using code first). Otherwise can you share the code showing how you are deleting records.

    Regards,


    Ovais Mehboob

    Thursday, November 1, 2012 5:11 PM
  • Hi mio63,

    Could you please post the code you delete the entities here? This is, so we
    can help you more effectively.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, November 5, 2012 1:56 AM
    Moderator
  • Database first. The relationship is as far as I can see correctly defined in the model. Here is the diagram:

    And this is the relationship between db_BaseHeading and db_BaseHeadingElement:

    I'm trying to delete on instance of db_BaseHeading and all related db_BaseHeadingElement. Simple code like this:

    while dbBase.n_BaseHeadingElement.Count > 0

            dbBase.n_BaseHeadingElement(0).Delete()

    end while

    dbBase.Delete()

    SaveChanges()

    And yes, all db_BaseHeadingElement children are in the cache before deleting. If I insert an additional SaveChanges between the loop and the parent delete then it all works. When like now, then SaveChanges tries to delete parent record first.

    Tuesday, November 6, 2012 8:53 AM
  • Please see the sample code below. I have two tables one is "Parents" and the other is "Children".

    using (rel1Entities entity = new rel1Entities())
    {
       var parent=entity.Parents.FirstOrDefault();
       while(parent.Children.Count>0)
       {
          var item = parent.Children.ElementAt(0);
          entity.DeleteObject(item);
       }
       entity.DeleteObject(parent);
    }


    Ovais Mehboob


    Tuesday, November 6, 2012 11:02 AM
  • That's pretty much what I do; loop and delete children, then delete parent.

    Let it be said that I use DevForce from IdeaBlade on top of EF, however DF is not in any way involved in maipulating the database, that is all left to EF.

    This is all done in an Silverlight application, so having to kick off two roundtrips to the server - one to delete children and then anotjer to delete parent - is not a valid resolution.

    Anyone knows how EF generates SQL and how the sequence of statements is decided? Obviously there is something with the model resulting in this behaviour, but I'm totally unable to find any documentation at all describing this.

    Mikael

    Friday, November 9, 2012 8:37 AM
  • We couldn't control the execution sequence of the command, and EF has no convention to generate the sql. Have you check the sql and the command sequence from the SQL Profiler?

    Go go Doraemon!

    Monday, November 12, 2012 1:48 AM
  • Yes, SQL Profiler shows that the first SQL command kicked off is the one to delete the parent record. I first checked the entity manager to verify that both the parent record and the child records were marked for deletion. Still, the first SQL command issued tries to delete the parent.

    I find this situation quite hopeless; I have trawled forums and KBs all over the Internet with no luck. I haven't been able to find any information whatsoever on why this happens.

    As it seems for now I will have to implement a two-roundtrip solution; deleting the child records first and commit, then second roundtrip to delete the parent records. Performance will suffer, but I can't see any other possibility. Bad, really bad.

    Mikael

    Wednesday, December 5, 2012 3:52 PM
  • If performance is that important, fix the database to cascade the delete.  Cure the disease instead of the symptoms.

    This signature unintentionally left blank.

    Friday, December 7, 2012 1:10 PM
  • Performance is always important, especially in distributed applications where the client can run miles and miles and multiple noetwork nodes away from the server. Also, I'm not at liberty to change the database schema; that would have unpredictable impact on other legacy applications that rely on an update or delete failing if there are dependant rows in another table.This approach may be sufficient in a one-man-show project, but not in a real-world application.

    Finally, the disease would actually be EF trying to be clever about the order of execution. Since I have no impact on how EF does this, I'm left treating the symptoms of a chronic disease.

    Thursday, December 13, 2012 9:55 AM
  • Performance is always important, especially in distributed applications where the client can run miles and miles and multiple noetwork nodes away from the server. Also, I'm not at liberty to change the database schema; that would have unpredictable impact on other legacy applications that rely on an update or delete failing if there are dependant rows in another table.This approach may be sufficient in a one-man-show project, but not in a real-world application.

    Finally, the disease would actually be EF trying to be clever about the order of execution. Since I have no impact on how EF does this, I'm left treating the symptoms of a chronic disease.

    I have same error with incorrect order of sql statements, but still did not know how to fix it.
    • Proposed as answer by Nurkanat Saturday, December 21, 2013 7:35 AM
    Saturday, December 21, 2013 7:14 AM