Best Practices - checking if child record exists

[Derek]

Looking for suggestions about the best way to go about something.

We have an employee; the employee can have payroll, training, and several  other child tables associated with a row. We allow deleting of the employee, but only if there are no rows in any of the child tables. Previously we used a database function which checked for any child records in any of the tables and returned a value indicating whether deletion was allowed or not for that employee.

What would the best approach to doing this using Data Access? Or is this still the kind of thing best left to stored procedures; and if so, can someone walk me through how we could access this function via Data Access?

[Kristian Nikolov]

Hello Derek,

Thank you for contacting us.

Generally, unless your model has been configured to support Cascade Delete, you will not be able to delete a database entry which has child entries related to it. So if you attempt to delete an employee that has entries in its related child tables, you will receive a DataStoreException when calling the SaveChanges() method of the context. It is possible to use this exception to handle scenarios where an entry that should be deleted can have child records.

In case you wish to prevent the attempt of deleting an entry with child records from the start, you will indeed need to check if such records exist. You can do this using the Navigation Properties of the persistent class mapped to the Employee table. For example if the Employee table has a child table Account, in your model the Employee persistent class will generally have a property Account which is a reference to the related child object. You can check whether the property is null and only then delete the employee. The following code snippet is an example:

if (employeeToDelete.Account == null)
{
    context.Delete(employeeToDelete);
    context.SaveChanges();
}

We recommend you consider which would better suit your specific scenario.

I hope this helps. Should you have additional questions, feel free to post in our forums again.

Regards,
Kristian Nikolov
Telerik