Hi,

I don't understand why you don't support "DeleteMulti for entites in a hierarchy of type TargetPerEntity"

Wouldn't be possible to do the job in a transaction ?

Note: if already in a transaction, then add queries in the transaction, no ?

Could you please post an example of what's wrong with DeleteMulti, and how you would like to use that code with transactions?

LLBLGen Pro version and runtime library version?

Wally wrote:

Hi,

I don't understand why you don't support "DeleteMulti for entites in a hierarchy of type TargetPerEntity"

Wouldn't be possible to do the job in a transaction ?

Note: if already in a transaction, then add queries in the transaction, no ?

It's not possible without a temptable. Let me explain Say you have the hierarchy Animal<-Mamal<-Cow

The question is about directly deleting cows matching a filter. To be able to delete a cow, we've to remove a row from cow, mamal and animal. In that order, otherwise FK constraints fail. However the deletes are done with DELETE FROM statements for all cows.

Say I want to delete the cows of farmer Smith. DELETE FROM Cow WHERE Owner="Smith'

that was easy.

Now, we've to delete the Mamal rows of these cows. You can't filter anymore on Cow, the rows are gone. So you have to store the Cow rows first in a temptable, then when you want to delete Mamal rows, you've to join with that set in the temptable and first store the mamal rows in another temptable and then delete the mamals, same process for animal.

Due to this complexity we don't support this also because not all databases support temptables.

Thanks a lot for the answer.

I understand the complexity, the cost in performance and also the problem of differents databases options.

I don't know about tempTable or CascadeDelete suport into major Database company but I would suspect that "Cascade Delete" is now a standard option (and for a while).

I would have seen, if i'm right, one approach that would have been more flexible... I think it would have been possible to detect "cascade delete" in hierachies in the code generation tool (LLBLGen) and let do the delete on the top parent. I think it would be more easier and faster to program for the coder, and the performance would be in an another order of speed. I don't say it is trivial to add that functionality in the tool but I don't see what would make it impossible to do it. For database not supporting cascade delete, you just manage the case in the same way as now... like no cascade delete exists between hierarchies and throw the same exception as we get actually.

Eric

I guess "cascade delete" isn't retrievable with the metadata of the table/relation.

You guess ????? ... A quick search (5 minutes) give me that : http://msdn.microsoft.com/en-us/library/st1t2c35.aspx

I did not really investigate but if it's not the best way, I'm sure there is other ones. I don't really have time to look more for that but I'm sure it's retrievable.

To Frans... Please look at my previous post... Thanks !

Wally wrote:

You guess ????? ... A quick search (5 minutes) give me that : http://msdn.microsoft.com/en-us/library/st1t2c35.aspx

Please stay polite, thanks. We're not working with SQLServer alone.

I did not really investigate but if it's not the best way, I'm sure there is other ones. I don't really have time to look more for that but I'm sure it's retrievable.

Yes it's retrievable from FK constraints, that is, in some databases which support it (not always)

In theory it could be used to produce a delete on the root of the hierarchy, and then delete the types based on the filter specified on the leafs. Though this is far from trivial and as said, not every db supports cascading deletes, nor is every database as friendly to provide such information.

Thanks.

To Walaa (and Frans)... Sorry if you received my answer as impolite or rude. I guess Walaa try to help the best as he can. I just questionned myself about the value of the post. A "guess" appear to me to add more confusion... but I can be wrong. But yes, I should have at least say thanks and pass over that. I'm too direct ! Diplomacy is very not one of my strenght, I have to work on that !

To Frans, Can I understand from you answer that due to the difficulty of realisation vs advantages for users (or part of it), I cannot consider my point as a possible suggestion ? Or something to be added as a very low priority wish list point ?

Wally wrote:

Thanks.

To Walaa (and Frans)... Sorry if you received my answer as impolite or rude. I guess Walaa try to help the best as he can. I just questionned myself about the value of the post. A "guess" appear to me to add more confusion... but I can be wrong. But yes, I should have at least say thanks and pass over that. I'm too direct ! Diplomacy is very not one of my strenght, I have to work on that !

No worries, Wally

To Frans, Can I understand from you answer that due to the difficulty of realisation vs advantages for users (or part of it), I cannot consider my point as a possible suggestion ? Or something to be added as a very low priority wish list point ?

It's a very fragile feature (DBA removes cascading delete, your application doesn't work anymore), and the query to produce isn't that simple (lots of joins). Mainly due to the fragileness of the feature we won't proceed with your suggestion. Also because not all databases support it, will make it a bit of a gamble for users to use the feature.

I'm a bit disapointed.

I don't think DBA would remove cascade delete without any good reason/talk to developper. Cascade delete is the good way to enforce good management by removing possibilities to left orphin everywhere. Yes, that's true that DBA are affraid of "Cascade Delete" but they will eventually grow (learn and undertsand the effiencency of it)... (I know there is also negatives points about the use of them).

Cascade delete is the most usefull unused options of database. But in an object world with hierarchies it is just wonderfull and so usefull.

All major database company/openSource support "Cascade delete". I made a quick look and cannot find one that do not support. (Yes, I have not make a complete roundtrip but check most of them).

Yes it is complicated and it would probably take days to implement that option. But, according to few articles you wrote, they let me think that you are a "perfectionnist". I still think that you like things very well made but I think that you are also realistic and probably stuck with development and few times.

I would have appreciate if you would add my suggestion as a very low priority suggestion (althought you had not the intention to implement it in short or long period of time)... Just in case someday you have nothing to do... .

But I "Guess" you answered that because you know you will never have time to ever think about it. That "cascade delete" is not used very often and hierachies probably either. Plus DeleteMulti is probably not used that much too. Then the importance of it is like very very very low !!! Also, I "guess", that probably I would have more chances to get a positive answer if I would have been more diplomatic...

Anyway... I appreciate that you took the time to answer my question and you honesty, really! Thanks a lot...

To Walaa (and Frans)... Sorry if you received my answer as impolite or rude. I guess Walaa try to help the best as he can. I just questionned myself about the value of the post. A "guess" appear to me to add more confusion... but I can be wrong. But yes, I should have at least say thanks and pass over that. I'm too direct ! Diplomacy is very not one of my strenght, I have to work on that !

You are absolutly right, my responce wasn't helpful at all. I'm sorry for that.

Hello Walaa,

I looked to send you direct email. I created a linkedin user account but I can't send direct email... Also websense(web filters) here block your blog profile page. Then I have to say it here... I looses jobs because of my bad character (emotions) / Zero diplomacy / Low respect. I'm sorry, I should have not say that, it just put me in that mud now. Thanks for every tried you do on each of my post. It often help me.

No hard feelings.