none
Invalid ChangeSet Submit Order when using Cross-table Check Constraints with Entity Framework RRS feed

  • Question

  • I am running into an issue where the order that a ChangeSet is submitted to the database is violating a Check Constraint on one of my tables.  I am using a Check Constraint on one of my tables that calls out to a table-valued function which runs a query and returns any records that would violate the constraint.  Here is an example of my check constraint.

    Alter Table ParentA
    Add Constraint CK_ParentA_ValidData
    Check (dbo.tvfGetInvalidData() = 0)

    My database model is as follows.  There are 5 entities; ParentA, ChildA, ParentB, ChildB, Lookup1.  ChildA has a reference to ParentA and ChildB has a reference to ParentB.  ChildA also has a reference to ChildB.  ParentA and ParentB both have a reference to Lookup1.

    My issue comes into play with the constraint that I have built in to make sure that the ParentA and ParentB tables have valid records after an update is made.  Basically, since ParentA and ParentB are somewhat connected through ChildA and ChildB, I need to make sure that the references to Lookup1 in both parent tables match.  So if the Lookup1 reference in ParentB changes, I need to delete the ChildA records that reference a ChildB entity that has a reference back to ParentB.  Unfortunately, there wasn't a way that I could build in this constraint using normal foreign key relationships, so this was the route that I had to take.

    Now, in my Silverlight application, I have a control that allows records from ParentB to be updated.  However, when a record is updated through my application, I also need to delete the ChildA records within the same SubmitChanges call.  Since the update occurs first and the deletion of ChildA records occurs once the user clicks the "Save" button, the ChangeSet has the Update first and the Delete second.  As I understand it, Entity Framework is supposed submit the changes in the correct order depending on references, but since there isn't a FK reference I'm guessing it doesn't know that the delete needs to happen before the update in order for the check constraint not to fail.  So when the SubmitChanges runs, it tries to update first and the check constraint fails.

    Has anyone ran into an issue like this before?
    Tuesday, September 11, 2012 9:20 PM

Answers

  • Hi BTWalleye,

    Order of commands execution is controled by EF, we couldn't specify it. What we can do to specify the order is calling SaveChanges method separately.

    Best Regards


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

    Monday, September 17, 2012 3:02 AM
    Moderator

All replies

  • In this scenario, the best way is to use stored procedure. You need to delete ChildA record everytime when you update the ParentB record, encapsulate the logic into a stored procedure and execute it in the Entity Framework is obviously a better way.


    Go go Doraemon!

    Thursday, September 13, 2012 2:01 AM
  • Thanks for the suggesstion.  This sounds like a good solution for most situations, however in my situation is a little unique.  I have logic in the PersistChanges method that needs to run for each data change.  So, I need both the update and delete transactions to be handled through the application so that this logic gets executed.  If I handle the data update's through a stored proc, I don't think this logic will run.

    Just another twist in the puzzle!

    Thursday, September 13, 2012 1:57 PM
  • You can use transaction in stored procedure and write the two operations in one stored procedure. Please look at the link, there's an exmple.

    http://blog.sqlauthority.com/2010/06/02/sql-server-stored-procedure-and-transactions/


    Go go Doraemon!

    Friday, September 14, 2012 1:51 AM
  • I'm still not sure that this will work in my scenario.  Since I am overriding the "PersistChangeSet" method in order to log specific details of the transactions that are taking place (such as the entity that is changing and the action that is performed), I need this method to be called for both the update and delete transaction.  If I  handle both of them through a stored procedure, it seems to me that I would have to reimplement this logging logic into the stored procedure, which I want to avoid doing for maintainence purposes.  Unless I'm missunderstanding the way stored procedures are handled when using Entity Framework and Ria Services?

    Friday, September 14, 2012 1:27 PM
  • Hi BTWalleye,

    Order of commands execution is controled by EF, we couldn't specify it. What we can do to specify the order is calling SaveChanges method separately.

    Best Regards


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

    Monday, September 17, 2012 3:02 AM
    Moderator
  • That's what I was thinking would probably be the best solution in this case.  If I want to keep the contraint, i will probably just need to split up my process into multiple SaveChanges calls.

    Thanks for the feedback everyone.

    Monday, September 17, 2012 1:34 PM