Is ES prepared to support cascade delete? I could set this directly in Sql2005, but what about the other DBs (particularly Sql2000, Oracle & Mysql), they dont seem to have this feature builtin...I guess

So is there anything I could set in ES to assure that if I delete a(some) row(s) (whether by using an entity or a collection), I would firstly delete all its dependant children (FK constraint) ?

If not, will ES someday suppport this?

All the databases you mention, as well as, PostgreSQL, Access, and VistaDB let you set various options for handling cascades of deletes and updates per foreign key. That is the preferred approach both for maintaining referential integrity and because the cascade happens within the database, rather than sending a bunch of deletes over the wire.

Based on previous posts on the subject

http://community.entityspaces.net/forums/thread/5090.aspx
http://community.entityspaces.net/forums/thread/5582.aspx

We started to add a DeleteWithCascade method to the entity class, but after testing, we decided it was too powerful and dangerous, and we pulled it before release. Cascading deletes are the exception rather than the rule. If, for some reason, you cannot define them in the database, we felt that the developer should add their own custom methods, on a case by case basis.

In one test case against our hierarchical test table, we deleted an upper level employee. Not only were all related Customers, Orders, and OrderItems deleted, but all the employees he supervised were deleted, along with all their related Customers, Orders, and OrderItems. One method call wiped out 90% of the database.

So, for the time being, it is off our ToDo list, and left up to the developer.

---

David Neal Parsons
www.entityspaces.net

I understand your concern with having a too powerful feature that may exceed the limits of the user expectation. On the other hand, I see this as having the same power as someone that sets this directly in the database, since the last option would also be able to destroy the whole database in just one single shot! So, put side by side, the only difference between them is that enabling one to directly set this as a property in ES is far more elegant and (over all) more robust, in the sense it treats the same way, as well as enables the feature whichever database you are currently using. My sincere votes for "reimplementing" this (maybe in ES2008) :)

Thanks!

 

On the contrary. A method in all entity classes that deletes all children is not the same as setting cascading delete options on a per foreign key basis in the server. For example, you might leave the cascading delete action as restricted or none (the default) for all employee related foreign keys, and only set the cascade option to DELETE for the one foreign key between Orders and OrderDetails.

To accomplish the same granularity in EntitySpaces would require a much more complicated template UI that let you specify per foreign key options, and significant changes to the generate classes and underlying architecture to accommodate those options.

---

David Neal Parsons
www.entityspaces.net

Well I agree, but I see this as a "all or none" approach. Except for rare situations, the common case is that in which one sets the whole DB to be cascade deleted, rather than selecting in a per table/key approach. So a tip would be having this as an option (unchecked by default) during the classes generation (as you have for WPF for instance), so anyone that needs it might check and have it as a replication of a whole cascade deleting settings in the database.

Moreover, I guess having this built-in in ES may be faster than setting this directly in the database; Ive heard of performance bottlenecks in the Internet, probably due to a bad access plan of its queries (to check if the children records exist)....

It's really not that hard to do the cascading deletes yourself in a nice transaction. In fact I prefer that over any proposed built-in method and even over doing it in the database (I prefer to not have business logic dictated like that).

I can totally understand the ES team not wanting to implement this, nobody wants some developer blaming them for making it too easy to make their system go boom. 

 

Not so easy dude...consider the fact that sometimes its not possible to set a cascade delete (for instance when you have multiple FKs pointing to the same PK)....then you have to implement cascade delete directly in C# !

Either I don't understand what you are saying, or you didn't understand me because I was talking about how I prefer to do the cascading deletes in code (C#).

paschoal1,

If you have multiple FKs pointing to the same PK, then you have set CASCADE to DELETE for all of them, or none of them. The same is true, even if you code it in C#. The FK constraints prevent you from accidentally leaving orphaned records either way.

---

David Neal Parsons
www.entityspaces.net

Right, you can set the cascade feature differently on each FK.

---

EntitySpaces | Twitter | BLOG | Please honor our Software License

 

Sorry David, maybe I didnt quite understand what you mean. Picture the situation below:

Table Person: IdPerson (PK), Name

Table Orders: IdPersonWhoBought (FK), IdPersonWhoSold (FK), PurchaseDate, Price

So you can see that both the fields IdPersonWhoBought and IdPersonWhoSold point to IdPerson in Person table.

In this situation I cannot set cascade delete to both IdPersonWhoBought and IdPersonWhoSold !!!

Thats what I meant originally...

 

 

Gotcha. I thought you meant the FKs were in two different tables. Your situation would have to be done in code. But, it also highlights why it would be difficult for us to automate. In this case, you would loop through the table and MarkAsDeleted any row where either PersonWhoBought or PersonWhoSold was the same as the Person you wanted to delete.

We would be in the same situation as SQL Server. If we did Person.PersonWhoBoughtCollection.MarkAllAsDeleted, then Person.PersonWhoSoldCollection.MarkAllAsDeleted, then Person.MarkAsDeleted. Then, any row where they were the same, we would be trying to delete a row that was already deleted. I realize, based on column names, that is not likely in this case, but consider a Client table that has both PartnerInCharge and Manager fields, both pointing to the Employee table. They could easily be the same person.

The potential for cyclical or multiple cascade paths is fairly easy for a human to cope with, but not SQL Server, or EntitySpaces. We do not have the understanding of the database design that you do.

---

David Neal Parsons
www.entityspaces.net

Well done, I will follow the approach of cascade delete setting directly in the database, complementing it with code where needed...