2

I have an application with a database that doesn't allow cascading deletes. When a user goes to delete an entity, I would like to display a popup saying something generic like, "This record has dependencies that must be deleted first".

To support this, I was hoping to write some code that could generically take an entity, check for virtual properties, and if they exist, iterate through them to see if any records exist. If it finds one, it would immediately return true.

The goal would be to return a true/false with as little lazy loading as possible.

Before I write the code to do this, just wanted to see if anyone has done this before. I guess I could just look for the exception, but I generally don't like coding for exceptions.

  • 1
    It's unclear from just your verbal description what your new code is going to look like. Why would it return true, for example; doesn't true usually signal success and not failure? This might be one of those cases where it's easier (and proper) to handle the exception instead of getting clever just to avoid them. – Robert Harvey Mar 27 '17 at 18:50
  • @RobertHarvey Basically, what I'm writing is a method named HasChildren and it either returns true or false. Coding for exceptions is going to limit what I can do with it, so it's not simply about style. Exceptions are expensive and I want to avoid it. – AnotherDeveloper Mar 27 '17 at 18:57
  • It sounds like you're accessing a database, probably over a network. The exception is the cheapest part of this process. Measure, don't guess. – Robert Harvey Mar 27 '17 at 19:04
  • @RobertHarvey I specifically added the line about not wanting to use exceptions because I knew people would just tell me to check the exception. I already have that solution. I don't want to argue about the what my choices are, I want to see if anyone can solve the specific question I asked on the forum. I think I have made this clear by now. – AnotherDeveloper Mar 27 '17 at 19:12
  • 2
    @AnotherDeveloper I agree w/ Robert Harvey and often use exceptions in this scenario with great results. Maybe you could avoid the inverse XY effect, where people answer a question you are not interested in by misleading us a bit more effectively. You could say "I'm working with an ancient legacy schema that has no referential integrity constraints and I'm not allowed to add any (Big Iron DBA Voodoo, don't ask) and I hope to use Entity Framework to find dependencies. Here is what I have tried. Here is what I don't like about it...Ahh as I type this I see your answer. That may help. – joshp Mar 27 '17 at 22:12
2

I would go with @Robert's approach of just trying to delete the record first. If there are no dependencies, then it deletes. If not, then you get a constraint violation.

If you get a constraint violation, you will have to get the list of foreign keys for that table and then query each table for the Id you wish to remove. If the count is greater than 0, you have a dependency. Also, the constraint violation error will probably list the table(s) involved so you could parse out the constraint error to resolve the issue as well.

There are several existing stack overflow answers that discuss how to identify foreign keys on a table.

Bear in mind if you have deep and nested relationships, there could be many dependencies to check and list.

| improve this answer | |
  • Thanks Jon. There are a few ways I can do what I'm trying to do, I just want to see if anyone has a way to do it by generically checking the relationships. – AnotherDeveloper Mar 27 '17 at 19:15
0

This is pretty rough, but it accomplishes what I was trying to do:

internal static bool HasChildren(object obj)
{
    //Get Properties
    var props = obj.GetType().GetProperties();
    foreach (var property in props)
    {
        //Look for Navigation Properties
        if (property.GetGetMethod().IsVirtual)
        {
            var value = property.GetValue(obj);
            //Typically instantiated, but just in case, look for null
            if (value != null)
            {
                //Since most navigation propertie are HastSet<T>, it's going to be tough trying to
                //reflect on the object using a type. Going to look for a count prop instead.
                if (property.PropertyType.GetProperty("Count") != null)
                {
                    var count = property.PropertyType.GetProperty("Count").GetValue(value);
                    if (Convert.ToInt16(count) > 0)
                    {
                        return true;
                    }
                }
            }
        }
    }
    return false;
}
| improve this answer | |
  • As a person who knows near nil about Entity Framework, my first question, Does this generate a lot of individual query requests, over a network? Does that matter to you? Second question would be whether you like the way this will handle concurrent changes. Will it allow for process B to insert a dependent object just after process A has found no dependencies, but before A proceeds to do a delete. I.E. are you going to end up with the exception anyway, or worse, the dangling dependencies? Does that matter? – joshp Mar 27 '17 at 22:23
  • @joshp Question 1) I'm also worried about this code doing unexpected lazy loading, specifically "property.GetValue(obj);". But it does kick out immediately if it finds a child. Still testing it. Question 2) Yes, that is definitely something this code will not handle well. The good news is I'm not replacing the exception handing that would be created by violating referential integrity. My main concern is that this may not be thread safe, but I can always make it non static – AnotherDeveloper Mar 27 '17 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.