Entity Framework: Check all relationships of an entity for foreign key use

Question

I have an entity, let's call it CommonEntity that has a primary key used as a foreign key in many other entities. As the application is developed these links will continue to grow.

I'd like a way to see if CommonEntity can be safely deleted (i.e. it's not used by any other entities).

I realise I can do

if(!ce.EntityA.Any() && !ce.EntityB.Any() ... && !ce.EntityN.Any())
{
   //Delete
}

but I'm hoping for a way to just check all of the relationships automatically, as I don't love the idea of having to come back and change this code manually every time we add a new relationship. Perhaps there is something in EF4+ that I'm not aware of?

I thought it might be possible to use a transaction scope to just try and delete the object and roll it back if it fails, but I wasn't sure if there were any adverse side effects with this approach.

Is there a better approach?

EDIT: Looks like VS2012 has used EF5 even though the project is .Net 4, so it has created the model with POCOs even though it was generated from a DB.

Solution 1

Just let it fail. If the entity has many relationships, that verification could be really heavy.

public bool TryDelete(int id)
{
    try
    {
        // Delete
        return true;
    }
    catch (SqlException ex)
    {
        if (ex.Number == 547) return false; // The {...} statement conflicted with the {...} constraint {...}
        throw; // other error
    }
}

Solution 2

You can use Reflection for this (if you don't want use "Fail Delete On SQL") I write this because I dont want to DELETE Entity, just want to know if its related to any or not !

 public static object GetEntityFieldValue(this object entityObj, string propertyName)
        {
            var pro = entityObj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).First(x => x.Name == propertyName);
            return pro.GetValue(entityObj, null);

        }

 public static IEnumerable<PropertyInfo> GetManyRelatedEntityNavigatorProperties(object entityObj)
        {
            var props = entityObj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.CanWrite && x.GetGetMethod().IsVirtual && x.PropertyType.IsGenericType == true);
            return props;
        }

public static bool HasAnyRelation(object entityObj)
        {

                var collectionProps= GetManyRelatedEntityNavigatorProperties(entityObj);


                foreach (var item in collectionProps)
                {
                    var collectionValue = GetEntityFieldValue(entityObj,item.Name);
                    if (collectionValue != null && collectionValue is IEnumerable)
                    {
                        var col = collectionValue as IEnumerable;
                        if (col.GetEnumerator().MoveNext())
                        {
                            return true;
                        }

                    }
                }
               return false;
}

NOTE that : Context must not Disposed and Proxy Must Be Enabled AND KNOW THAT IT WILL GET ALL RELATED RECORD TO MEMORY (IT'S Too Heavy)

Comments

[BenC3]

If I let it fail, will it automatically roll back any successful cascading without a transaction scope?

[BenC3]

Found the answer - SaveChanges() uses a transaction, so any failures will be rolled back: msdn.microsoft.com/en-us/library/bb336792.aspx

[Gwasshoppa]

This is a really good way I think, instead of doing MyClass.Any() || MyOtherClass.Any() etc etc. The one thing I added was an Ignore attribute to allow you to decide to Not Include the Property in the HasAnyRelation() The GetManyRelatedEntityNavigatorProperties() looks like this... var props = entityObj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => x.CanWrite && x.GetGetMethod().IsVirtual && x.PropertyType.IsGenericType && (x.GetCustomAttribute(typeof (IgnoreIsDeletableAttribute)) == null) );

[gezanoletti]

Leting it fail is the best solution for me. If you checks for all relations you can generate circular dependencies with other subsystems. There's a drawback, it's hard to show to the user which relation is causing the error.

[dnxit]

It'll only check if it has any navigation property or not it will not check if it has any dependent data or not.

[Krishna]

what if ondeletecascade is turned on. it wont fail then would it?