How to implement recursive deletion?

Question

I have the following situation:

Customers contain projects and projects contain licenses. Good because of archiving we won't delete anything but we use the IsDeleted instead. Otherweise I could have used the cascade deletion.

Owkay I work with the repository pattern so I call

customerRepository.Delete(customer);

But here starts the problem. The customer is set to isdeleted true. But then I would like to delete all the projects of that customer and each project that gets deleted should delete all licenses as well.

I would like to know if there is a proper solution for this. It has to be performant though.

Take note that this is a simple version of the actual problem. A customer has also sites which are also linked to licenses but I just wanted to simplify the problem for you guys.

I'm working in a C# environment using sql server 2008 as database.

edit: I'm using enterprice libraries to connect to the database

Cheers, M.

Answer 1

One option would be to do this in the database with triggers. I guess another option would be use Cascade update, but that might not fit in with how your domain works.

Personally I'd probably just bite the bullet and write C# code to do the setting of IsDeleted type field for me (if there was one and only one app accessing the DB).

Answer 2

This totally depends on your DAL. For instance NHibernate mappings can be setup to cascade delete all these associated objects without extra code. I'm sure EF has something similar. How are you connecting to your DB?

If your objects arent persisted, then the .NET GC will sweep all your project objects away once there is no reference to them. I presume from your question though that you are talking about removing them from the database?

Answer 3

I recommend just writing a stored procedure (or group of stored procedures) to encapsulate this logic, which would look something like this:

update Customer set isDeleted = 1
where  CustomerId = @CustomerId

/* Say the Address table has a foreign key to customer */
update Address set isDeleted = 1
where  CustomerId = @CustomerId

/* 
   To delete related records that also have child data,
   write and call other procedures to handle the details 
*/
exec DeleteProjectByCustomer(@CustomerId)

/* ... etc ... */

Then call this procedure from customerRepository.Delete within a transaction.

Answer 4

If your relationships are fixed (i.e. a license is always related to a project, and a project to a customer), you can get away with not cascading the update at all. Since you're already dealing with the pain of soft deletes in your queries, you might as well add in the pain of checking the hierarchy:

SELECT [...] FROM License l
JOIN Project p ON l.ProjectID = p.ID
JOIN Customer c on p.CustomerID = c.ID
WHERE l.IsDeleted <> 1 AND p.IsDeleted <> 1 AND c.IsDeleted <> 1

This will add a performance burden only in the case where you have queries on a child table that don't join to the ancestor tables.

It has an additional merit over a cascading approach: it lets you undelete items without automatically undeleting their children. If I delete one of a project's licenses, then delete the project, then undelete the project, a cascading approach will lose the fact that I deleted that first license. This approach won't.

In your object model, you'd implement it like this:

private bool _IsDeleted;
public bool IsDeleted
{
   get
   {
      return _IsDeleted || (Parent == null ) ? false : Parent.IsDeleted;
   }
   set
   {
      _IsDeleted = value;
   }
}

...though you must be careful to actually store the private _IsDeleted value in the database, and not the value of IsDeleted.