NHibernate - How to find if a referenced object can be deleted?

Question

I have an object called "Customer" which will be used in the other tables as foreign keys.

The problem is that I want to know if a "Customer" can be deleted (ie, it is not being referenced in any other tables).

Is this possible with Nhibernate?

Thanks

Answer 1

It might be worth looking at the cascade property, in particular all-delete-orphan in your hbm.xml files and this may take care of it for you.

See here, 16.3 - Cascading Lifecycle

Answer 2

It's not possible directly. Presumably your domain model includes Customer's related objects, such as Addresses, Orders, etc. You should use the specification pattern for this.

public class CustomerCanBeDeleted
{

    public bool IsSatisfiedBy(Customer customer)
    {
        // Check that related objects are null and related collections are empty
        // Plus any business logic that determines if a Customer can be deleted
    }
}

Edited to add:

Perhaps the most straightforward method would be to create a stored procedure that performs this check and call it before deleting. You can access an IDbCommand from NHibernate (ISession.Connection.CreateCommand()) so that the call is database agnostic.

See also the responses to this question.

Answer 3

A naive solution will be to use a transaction. Start a transaction and delete the object. An exception will inform you that the object can't be deleted. In any case, do a roll-back.

Answer 4

Thinking in entities and relations instead of tables and foreign keys, there are these different situations:

• Customer has a one-to-many relation which builds a part of the customer, for instance his phone numbers. They should also be deleted by means of cascading.
• Customer has a one-to-many or many-to-many relation which is not part of the customer, but they are known/reachable by the customer.
• Some other entity has a relation to the Customer. It could also be an any-type (which is not a foreign key in the database). For instance orders of the customer. The orders are not known by the customer. This is the hardest case.

As far as I know, there is no direct solution from NHibernate. There is the meta-data API, which allows you to explore the mapping definitions at runtime. IMHO, this is the wrong way to do it.

In my opinion, it is the responsibility of the business logic to validate if an entity can be deleted or not. (Even if there are foreign keys and constraints which ensures integrity of the database, it is still business logic).

We implemented a service which is called before deletion of an entity. Other parts of the software register for certain types. They can veto against the deletion (eg. by throwing an exception).

For instance, the order system registers for deletion of customers. If a customer should be deleted, the order system searches for orders by this customer and throws if it found one.

Answer 5

I want to know if a "Customer" can be deleted (ie, it is not being referenced in any other tables).

It is not really the database responsibility to determine if the Customer can be deleted. It is rather part of your business logic.

You are asking to check the referential integrity on the database.

It is ok in non OOP world. But when dealing with objects (like you do) you better add the logic to your objects (objects have state and behavior; DB - only the state).

So, I would add a method to the Customer class to determine if it can be deleted or not. This way you can properly (unit) test the functionality.

For example, let's say we have a rule Customer can only be deleted if he has no orders and has not participated in forum.

Then you will have Customer object similar to this (simplest possible case):

public class Customer
{
    public virtual ISet<Order> Orders { get; protected set; }
    public virtual ISet<ForumPost> ForumPosts { get; protected set; }

    public virtual bool CanBedeleted
    {
        get
        {
            return Orders.Count == 0 && ForumPosts.Count == 0
        }
    }
}

This is very clean and simple design that is easy to use, test and does not heavily relies on NHibernate or underlying database.

You can use it like this:

if (myCustomer.CanBeDeleted)
    session.Delete(mycustomer)

In addition to that you can fine-tune NHibernate to delete related orders and other associations if required.

---

The note: of course the example above is just simplest possible illustrative solution. You might want to make such a rule part of the validation that should be enforced when deleting the object.