This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I have a menu entity, that self-references to build a hierarchy of menus. I can add and update menus ok, but if I try to delete a menu I receive a optimistic concurrency violation. The table structure is below. Id int PK In the database a relationship exists between ParentId and Id, with No Action set for the action in case of delete or update. However, when I check the SQL produced by LS when I perform a uow.Remove() and uow.SaveChanges(), two sql commands are issued, the first sets the DeletedOn date to now, this is correct as we use soft deletes, the second sql updates the same record to set the ParentId to null. This second command is obviously the one causing the problem. Basically it should not be issued anyway, as the ParentId property should not be set to null, it should leave it with its original value. Can anyone advise why this second sql command is being issued? Thanks
|
|
|
|
|
Hi Mark, The NoAction is only valid in a dependant child scenario where it will throw an exception when there are children present, where as what you are describing would be a parent scenario (you are deleting a child in what you have described above) so the behavior here is to set the reference to null so the parent unwires the child from its associated collection. That said - if you are running into an optimistic concurrency issue this means that both changes should have been issued on a single statement which clearly points to a bug in the unloading process. Is it possible for you to send through a small repro of this with a cut down version of your model and we can have a look into finding out what is causing this.
Thanks! Jeremy
|
|
|
|
|
Hi Jeremy,
Sorry I have not replied sooner, I was on a customers site all last week. To answer your comments above. Yes, in this scenario we are deleting a child record. But I am not sure why it would need to update the ForeignKey to a null value when that record is being deleted? What would happen later if we decided to undelete that record? We could not as the child parent relationship would be lost. Does this seeting to null work on all relatioships? Because that would definately be a problem for us. We have Orders with Header and Detail tables, if I soft-delete, a detail row, I still need to know what header it was assigned to for audit purposes, is that not the whole point of soft-delete? I have attached a zip copy of a model that contains just this self-referencing table. With regards to the code, I am just selecting a single record, in this case a child one, then using uow.Remove(menu); followed by a uow.SaveChanges(); That is it. Thanks for you help.
|
|
|
|
|
forgot the attachment. Sorry
|
|
|
|
|
Hi Mark, I think you may be misunderstanding what is happening in this scenario. The NULL is being set for the children of the deleted record, not the record itself, so the record which has been soft deleted (a child record) will still have _its_ parent id assigned, so it can be subsequently undeleted. Active children of that record however are having their parent set to null since it has been removed. If you want to retain that relationship so you could possibly roll it back later however that is an application specific concern so you will need to implement something custom (likely involving a backing field for holding a previous parent identifier, or some kind of stack if you allow reparenting etc). Thanks for the repro model - exactly what we needed to have a look at the optimistic concurrency bug - happy to say that we have now fixed it; The cause was that the second statement was errorneously having its versioned flag set which enables a check to make sure 1 row is affected (which unless you had 1 and only 1 child of the child record, will fail as you have seen). The update is in tonights nightly build (20101005), please let us know if there are any furthor issues :)
Thanks! Jeremy |
|
|
|
|
Hi Jeremy, Sorry to resurect this old posting, however, I am still experiencing this problem.. I have installed the lastest nightly build 30th Oct, but still get the same problem. Below is a trace output so you can see what is happening. The model has not changed wince the plast one I sent you. Thanks
UPDATE PoSMenu SET DeletedOn = '31/10/2010 14:10:43' WHERE (PoSMenu.LockVersion = 0 AND PoSMenu.Id = 103061) |
|
|
|
|
Hi Mark, If you run those statements manually do you get 1 rows affected and 0 rows affected as the results?
Jeremy |
|
|
|
|
Hi Jeremy, I am posting this reply again, as I just noticed when I paosted last time it did not save for some reason. To answer your question, yes if run the statements manually I get 1 and 0 rows affected.
|
|
|
|
|
Right, well that will be why the exception is being thrown (the 0 rows affected on the second statement), are you able to send through a small example of this with your model so we can have a look to see whats going on here? Based on just the SQL it appears that there is a mismatch between what the model is expecting to be in the database and what is actually in the database in which case the exception is being thrown for the right reasons, but I suspect this is not your intent given the issue being raised :)
Jeremy |
|
|
|
|
Unless I am misunderstading the SQL being produced, in this case the second result should be a 0. If we have a sef-referecing table, i.e. Menu, and Sub-Menus, but all of class menu. If I deleted a Menu, which had three Sub-Menus referening it, then I would expect the result to be 1 and 3 updated rows. But if I delete a Sub-Menu row, which has no Sub-Sub-Menus referencing it, then I would expect 1 and 0, as there are no other menus referencing the Sub-Menu. My understanding is, the first row updated it the Parent row, and the second rows updated are all it's children, is that not correct? If that is correct, then the SQL is correct, it is just the uow incorrectly interpreting the results that is wrong. |
|
|
|
|
Hi Mark, Just following up on this to see if its possible for you to send through a small repro of this so we can investigate further?
Jeremy |
|
|
|
|
Hi Jeremy, Unfortunately, I am currently preparing for a presentation due next week, so I am unable to make one in the short term. I can probably get something together next week. Thanks Mark |
|
|
|
|
Please find attached a small sample that shows the problem. Hopefully it will haelp you track down the source.
|
|
|
|
|
Hi Mark, Can you confirm the steps you use to reproduce this behavior? I can get it to occur if I Fill, then Delete and then Delete again (which deletes the soft deleted record being fetched from the Identity Map).
Thanks! Jeremy |
|
|
|
|
I am still unable to delete a child row from a self-referencing table. I have downloaded several verisons of LS since I first logged this, but I stiil have problems. Below is more details so hopefully you can reproduce this. This is the script to create the SQL Table and add some data is attached. Now with this data, if I open these entities into a List<>. I then use the uow.Remove(_menu) to delete the child row, i.e Id = 107581. When I the use uow.Save(), I receive the error. The output from the debug is as below. The first command is as I woudl expect. But the second command seems impossible. Namely "t0.Id = t0.ParentCode " there would never be a time where the Id and the ParentCode have the same value. also, it is looking for the Id same as the deleted Id, but with the DeletedOn = Null. But in the first command we set that record to equal the date and time. I also suspect the LockVersion would have changed from the first command? UPDATE --> Time: 10 ms
--> Time: 25 ms
I really need some help with this, this problem has been bugging me for several months. |
|
|
|
|
Hi Mark, Just a quick note to let you know we are looking back into this and will post an update once we have made some progress with this.
Jeremy |
|
|
|
|
Hi Mark, Just wanted to give you an update on this. Unfortunately it doesnt look like it will be possible for us to resolve this in the short term. We will keep working on this, but its going to require a change to improve our support for aliasing in hierarchy cases before this is covered, so this wont be something we can cover in LightSpeed 3.
Jeremy |
|
|
|
|
Hi Jeremy, Many thanks for the update. I presume from this that the problem is an actual Lightspeed problem? I am not asking this to gloat, just that I have been doubting myself for so long, it would be good to know I am not going crazy. Secondly, as I really do need a fix for this, is there a work around, i.e. if I remove the LockVersion column? Whilst that would not be a perfect solution, it would allow us to work. Thanks Mark |
|
|
|
|
Yes, this is definitely a LightSpeed problem. In terms of a workaround - removing the LockVersion will certainly stop it from blowing up with an exception, but the cascade delete will still be incorrect due to the aliasing issue.
Jeremy |
|
|
|
|
Hi Jeremy, Sorry to be a pain, but obviously I need to find a way to get this to work, I have numerous customers using this software, and at the moment each time they want to delete a child menu, I have to remote their system to go into the database to delete it. If the above won't work, is their any other workaround you can offer? Thanks |
|
|
|
|
No problem - we appreciate that this is an issue for you. One workaround would be to manage the cascade delete manually by unwiring the children first and then deleting the parent. Would that be workable for you (albeit still less than desirable)?
Jeremy |
|
|
|
|
Hi Jeremy, The problems we have experieinced are deleting the children. I will have a play at this end, now I have a better idea where the problem is, I work on alternatives. Thanks |
|
|
|
