Issues with deletion and entity inheritance

Posts   
 
    
zulu
User
Posts: 50
Joined: 25-Aug-2008
# Posted on: 22-Dec-2016 10:15:52   

Hi, I just noticed today an issue that I hope you can help me with.

I'm using LLBLGen 4.2 and the LLBLGen runtime framework.

Basically I have this entity structure

Payroll PayAdjustment PayAddition PayDeduction

PayAdjustment is a supertype for PayAddition and PayDeduction Payroll has a 1 to many relationship with PayAddition and PayDeduction.

to make it clear I can navigate like this

payroll.PayAdditions or payroll.PayDeductions

Now, if I delete a PayAddition or PayDeduction individually, the corresponding db row for the supertype PayAdjustment is deleted as well but if for example I delete Payroll, only the corresponding db rows for PayAddition and PayDeduction are deleted, but not the associated row of the supertype PayAdjustment.

What's going on?

The relationships in question have been set with delete CASCADE.

On another note, I did not create a relationship between Payroll and PayAdjustment even though it seemed logical because I wanted to have navigators for PayDeductions and PayAdditions. Probably it Payroll was related directly to PayAdjustment I wouldn't have the issue described above but I wouldn't have the navigators, unless there's a way to get the navigators that I'm not aware of.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Dec-2016 21:08:11   

So you have CASCADE DELETE set for the relation between the SuperType and the SubTypes, yet the SuperType is not deleted when the related rows on the SubTypes are deleted?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Dec-2016 10:17:19   

Additionally: there's a difference between the rule on the relationship and the actual rule on the foreign key constraint in the DB: the rule isn't enforced on existing relationships. First check if the rule is present on the FK in the database.

Frans Bouma | Lead developer LLBLGen Pro
zulu
User
Posts: 50
Joined: 25-Aug-2008
# Posted on: 26-Dec-2016 14:04:36   

Walaa wrote:

So you have CASCADE DELETE set for the relation between the SuperType and the SubTypes, yet the SuperType is not deleted when the related rows on the SubTypes are deleted?

Hmm.. not quite.

This is the structure:

Payroll PayAdjustment PayrollDeduction

PayrollDeduction is a subtype of PayAdjustment

Then I have a 1 to many relationship between Payroll and PayrollDeduction with cascade delete.

If I delete Payroll, PayrollDeduction gets deleted, but PayAdjustment, which is the supertype of PayrollDeduction, is not.

If on the other hand I delete a PayrollDeduction by itself, then the supertype gets deleted as well.

I did some tests using sqlserver profiler and :

  • when Payroll is deleted, a single 'Delete from Payroll' is run. When this is executed the cascade delete takes care of deleting PayrollDeduction obviously. But nothing happens to PayAdjustment like mentioned earlier.
  • When PayrollDeduction is deleted, 2 statements are executed. One to delete PayrollDeduction and one to delete PayAdjustment.
zulu
User
Posts: 50
Joined: 25-Aug-2008
# Posted on: 26-Dec-2016 14:10:02   

Otis wrote:

Additionally: there's a difference between the rule on the relationship and the actual rule on the foreign key constraint in the DB: the rule isn't enforced on existing relationships. First check if the rule is present on the FK in the database.

I checked and the rule is there on the FK constraing in the DB. It looks like LLBLGen handles things differently based on what is being deleted.

If a subtype is deleted, it generates sql to delete the corresponding row in the supertype table as well.

However, when a delete refers to an entity which has a 1 to many relationship with the subtype, a single sql statement to delete the root entity is generated, causing the row in the subtype table to be deleted thanks to CASCADE rules, but this causes the corresponding row for the supertype table to be ignored.

Maybe I'm doing it wrong and should use target per entity hierarchy? It wouldn't have these problems but in that case I would need to have the FK to Payroll on the supertype (PayAdjustment) and this would not allow me to have the two relationships I need between payroll and PayAddition plus PayDeduction.

I would only have the navigator Payroll.PayAdjustments instead of the navigators Payroll.PayAdditions and Payroll.PayDeductions.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Dec-2016 15:52:52   

when a delete refers to an entity which has a 1 to many relationship with the subtype, a single sql statement to delete the root entity is generated, causing the row in the subtype table to be deleted thanks to CASCADE rules, but this causes the corresponding row for the supertype table to be ignored.

Expected, because the cascade rule which deletes the subtype happens in the database, and not by LLBLGen.

What you can do is not to rely on DB Cascade rules to delete the PayDeduction or PayAdditions when deleting the Payroll.

Instead you can use LLBLGen's Unit Of Work, to add the PayAdjustments to the entities to be deleted along with the Payrol. Then you can use the DB Cascade delete rule to delete the corresponding subtype from the database.