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.

score 14 · Accepted Answer · answered Oct 12 '12 at 06:13

14

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
    }
}

answered Oct 12 '12 at 06:13

y34h

1,631
1
11
17

If I let it fail, will it automatically roll back any successful cascading without a transaction scope? – BenC3 Oct 15 '12 at 23:56
1

Found the answer - SaveChanges() uses a transaction, so any failures will be rolled back: http://msdn.microsoft.com/en-us/library/bb336792.aspx – BenC3 Oct 22 '12 at 05:18
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. – gezanoletti Jun 25 '18 at 21:22
2

what if ondeletecascade is turned on. it wont fail then would it? – Krishna Jul 29 '19 at 19:55

mX64 · Answer 2 · 2014-08-04T10:33:44.787

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)

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) );` — Gwasshoppa, Apr 08 '16 at 02:01

score 5 · Answer 3 · answered Oct 12 '12 at 04:49

5

You can try this:

var allrelatedEnds = ((IEntityWithRelationships)ce).RelationshipManager.GetAllRelatedEnds();
bool hasRelation = false;
foreach (var relatedEnd in allrelatedEnds)
{
    if (relatedEnd.GetEnumerator().MoveNext())
    {
        hasRelation = true;
        break;
    }
}

if (!hasRelation)
{
    //Delete
}

answered Oct 12 '12 at 04:49

After doing some research it appears that VS2012 uses a version of EF5 even when targeting .Net 4, so long story short it's generated POCOs for me even though I generated the model from a DB, so I can't cast to `IEntityWithRelationships`. My bad for not mentioning it! – BenC3 Oct 15 '12 at 23:52

score -1 · Answer 4 · answered Aug 15 '14 at 19:06

First find the entity which you want to delete using Find in EF and pass the entity to below function.. If the function returns true it means cannot be deleted and foreign data exists.. If function returns false it means no parent or child records and can be delete..

 public static bool DeleteCheckOnEntity(object entity)
   {
     var propertiesList = entity.GetType().GetProperties();
     return (from prop in propertiesList where prop.PropertyType.IsGenericType select prop.GetValue(entity) into propValue select propValue as IList).All(propList => propList == null || propList.Count <= 0);
   }

It'll only check if it has any navigation property or not it will not check if it has any dependent data or not. — dnxit, Nov 12 '18 at 12:16

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

4 Answers4

Linked