locked
Deleting and adding an entity set before save RRS feed

  • Question

  • Hi all,

    I have the following situation. There are two tables, let's say Master and Details, in a 1-to-many relationship. As is common in these cases, the Details list uses as its primary key the combination of the Master's ID and a Detail ID that's only unique for that specific Master.

    In my app, the user is supposed to edit a Master, including its Details, potentially adding, removing or reordering them, and then click a button to confirm the changes. At that point, I want to run some validation and then save the data to the database.

    Normally, I would do this by deleting all the existing Details that are associated to the Master that has been edited, and then rewriting the new Detail list. I would do this in a transaction, so that if something goes wrong I can roll back to the original Master and its Details.

    From what I've seen, however, the EF always performs inserts before deletions. This means that the procedure I described won't work, because inserting the new Detail list will violate key constraints. And if I do a SaveChanges after deleting the original Details, I'll lose the ability to rollback.

    I could work around this by comparing the two lists (original and modified) and do different things depending on whether a Detail was inserted, deleted or updated, but that's conceptually ugly.

    What's the "right" way to completely replace a list of Details?

    I've found this thread: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/2f1c0bd3-c71d-47e7-89d5-c8b6e34ca2e9 that seems to describe a similar situation, but I'm not sure it's quite the same (and it's from 2 years ago).

    Monday, November 15, 2010 6:09 PM

Answers

  • Okay! Turns out I had two problems:

    1) You need to call Load on the old Detail list before clearing it. I think that if you don't, the list only contains the references, and even if they're deleted, the actual entities can still conflict with new entities.

    2) If you're working on detached Detail objects as temporary storage, you can't add them to an attached Master. Now I'm cloning them into new Details, which seems to work.

    After fixing these, it seems to work.

    • Marked as answer by Zappo1980 Thursday, November 18, 2010 9:10 AM
    Thursday, November 18, 2010 9:10 AM

All replies

  • Why did you choose to delet all the existing Details that are associated to the Master that has been edited, and then rewriting the new Detail list ? How did you do the validation ?
    Tuesday, November 16, 2010 8:46 AM
  • I choose to delete and rewrite the Detail list because it's easier and cleaner. The user cannot edit or insert the Details one at a time (it doesn't make sense for the application context); they must enter all of them, then validate all of them, then write all of them to the database. Any modification requires the validation of the whole list (due to relationships between Details at the business logic level).
    Tuesday, November 16, 2010 2:08 PM
  • Hi Zappo1980,

    I would recommand you subscribe some events to validate or affect data before it is persisted to the database. EntityObject has class-level PropertyChanged and PropertyChanging methods. These two events are raised anytime any property in a particular entity class changes. For example, you can subscribe the EntityObject's PropertyChanged event, and run the validation in the event handler when the Master object is edited, retrieving the Details of the Master, and check the object's EntityState to see whether it is modified, to delete and rewrite the Detail list is easier and clerner, but it is not efficient I think.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 17, 2010 9:58 AM
  • Hi Alex,

    Thanks for your answer. I can't run validation on PropertyChanged/ing, because the Details can only be validated once the user has finished entering all of them. I can't validate partial data; an incomplete Detail list will always fail validation. A full Detail list, and only a full Detail list, must be written to the database in a single transaction; if it fails, the full previous Detail list must be restored. Under no condition I can have partial changes on the database. In any case, my problem is not validation - it's persistance.

    I'll attempt to simplify the problem for clarity: assume that the user never modifies any Detail. When he wants to modify a Master, the user always resets the Detail list to blank, fills it up, and then validates it and saves it.

    The EF apparently cannot do this. As soon as the user inserts a Detail, it will throw an exception because the new Detail (which, being the first, has ID 1) conflicts with the first Detail of the previous list, even if it has been deleted . That's my problem.

    Wednesday, November 17, 2010 10:26 AM
  • Hi,

    IMO the problem is that the ID is supposed to uniquely identify each entity. But here you are using the same ID for a previous entity you want to delete and for a new entity you are trying to insert.

    My starting point would be to browse the objects hold by the ObjectContext but I believe you'll find you actually have a single entity 1 for this object type. If yes, I don't see how you could workaround this except adding really something to distinguish between the deleted and inserted instance (basically something that will act as a primary key).

    If you find two entities you could perhaps inherits from the context to change the ordering of those operations.

    If possible, I would strongly recommend to always work with "true" primary keys (i.e. including never reusing the value).

     

    A possible workaround could be to perform deletion on a different context. You could then wrap the update of both context in an explicit transaction.

     

     

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Wednesday, November 17, 2010 12:55 PM Possible workaround ?
    Wednesday, November 17, 2010 12:45 PM
  • Hi (my previous message doesn't show up ?),

    I tried a simple repro and it seems to work. I create a single table with an integer primary key and a single row with ID 1. I delete this row, then insert a new row with the same key. When calling SaveChanges it works fine.

    I'm using EF 4 against SQL Server.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, November 17, 2010 1:12 PM
  • Hi,

    Changing the primary key to a unique number won't work. I still need to have a progressive number identifying Details, going from 1 to X for each Master. This will be a UNIQUE constraint on the database, even if it's not the primary key. The EF won't complain immediately, because it only cares about the primary key, but it will still throw a SQL error on SaveChanges due to violation of the uniqueness constraint (because it does insertions before deletions). Besides, forcing all primary keys to be single columns only would be a very weird requirement. Multi-column primary keys are very common in master/detail scenarios.

    As for your repro, are you using IDENTITY as a column type for your ID? If so, it seems to work but actually SQL Server is changing the IDs without telling you (try reloading the table, you'll see that the IDs are changed). That won't work in my case.

    Wednesday, November 17, 2010 2:36 PM
  • I thought the problem was to use the same primary key for both an entity that is deleted and another entity that is inserted (regardless of having a single or multicolumns key).

    Regarding my repro, the key is just an integer column. I create a (1,"A") row directly in the db. Then the EF code just retrieves this row as an entity and delete it. Then only creates a new (1,"B") entity (with the key being explicitely given client side). Then it calls SaveChanges which works fine and in my db I've got a single (1,"B") row.

    How is organized your code ? Are deletions submitted to the context before insertions or after ? Could it be that EF takes into account the order in which you transmitted the changes ?

    Or is my repro just too simplistic ? You could perhaps try a simple repro on a dummy table and see at which point you start to have this issue ? Also to be on the safe side do you use EF 4 ?

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, November 17, 2010 4:49 PM
  • Okay, I've done some more tests and you're right: the repro on a dummy table works. The problem seems to need a relationship to manifest.

    Here's a simplified test:

    Formula f = App.DatabaseEntities.Formulas.First();
    f.Ingredients.Clear();
    f.Ingredients.Add(new Ingredient() { ID = 1 });
    App.DatabaseEntities.SaveChanges();
    
    

    In this case, Formula is the Master and Ingredients are the Details. Before execution starts, Formulas contains one record, which has one Ingredient, which has ID 1. This example will fail on SaveChanges with a duplicate key error.

    Worth of note: before SaveChanges, App.DatabaseEntities.Ingredients (the table of all details) contains exactly one entry. So, in theory, a duplicate key should be impossible.

    I'm using EF 4 on VS 2010.

    Wednesday, November 17, 2010 5:12 PM
  • I still don't see my previous post ?!

    Since then I tried to insert before deleting and I see that the delete statement is still sent before the insert statement. So for now I don't see the same behavior but rather the opposite (i.e. deletes are done before inserts). Do you use EF 4 ? Could it be a confusion with Linq to SQL ?

    IMO you should try a similar simple repro and see how it behaves on your side. It would allow to find if this is something particular to your project (do you have self references that could perhaps confuse the ordering process ?) or if we really have a difference in how EF works on our computers...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, November 17, 2010 5:24 PM
  • Ah ! What if you explicitely call DeleteObject to delete the previous entity ? IMO Clear() clears the collection but doesn't change the state of the items so IMO you have no deletion but only an insertion.

    You could also test the value returned by ObjectStateManager.GetObjectStateEntries(EntityState.Deleted) to see if you actually have something marked for deletion...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Wednesday, November 17, 2010 5:37 PM
  • Hi Patrice,

    Thanks for your help. I've used GetObjectStateEntries to check, and Clear() does mark the objects for deletion.

    However, you got me thinking on the right track - I've found that if I add f.Ingredients.Load() before the Clear() on the example I posted above, then it works. I'm not sure why this is necessary: the Ingredients are correctly marked for deletion even if I don't do Load(), so it's not like the framework doesn't know about them. Still, it's a step forward.

    I'll need some time to apply this to the real case to see if we've got a solution; I'll get back to this thread.

    Wednesday, November 17, 2010 7:02 PM
  • Okay! Turns out I had two problems:

    1) You need to call Load on the old Detail list before clearing it. I think that if you don't, the list only contains the references, and even if they're deleted, the actual entities can still conflict with new entities.

    2) If you're working on detached Detail objects as temporary storage, you can't add them to an attached Master. Now I'm cloning them into new Details, which seems to work.

    After fixing these, it seems to work.

    • Marked as answer by Zappo1980 Thursday, November 18, 2010 9:10 AM
    Thursday, November 18, 2010 9:10 AM