Bulk Delete v3

In my last two posts (Bulk Delete with EF4 and Bulk Delete only for loaded entities), I showed you how to do Bulk delete with EF4.

However, there are some issues that I will try to fix with this new post:

  • Frans explained me that my solution was very limited to few providers and not optimal. He advised me to use “DELETE FROM FROM” syntax.
  • Danny remarked that there is an issue to enable correlated update queries. In order to fix it, I will execute my bulk deletes before my context SaveChanges in order to be able to add new row whith no risk that a bulk delete deletes it. Moreover, with my previous solution, we can have a desynchronized context in this case.
  • I also identified some issues with TPT, TPC, Vertical Entity Splitting, Horizontal Entity Splitting (so when we have several tables mapped to entity types) and also with Table Splitting.
  • I also identified a bug with my join clause if key properties name are different than columns name

First, I want to thank Frans and Danny for their remarks / advises.

So this is my new version. // Note that I explain it at the end of my post.

public static class ObjectSetExtension
{
    public static void Delete<TBase, T>(this ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate = null)
        where T : class, TBase
        where TBase : class
    {
        IObjectContextWithBulkOperations context = entitySet.Context as IObjectContextWithBulkOperations;
        if (context == null)
            throw new NotImplementedException();
        if (predicate == null)
            predicate = item => true;
        context.Delete(entitySet, predicate);
    }
    public static void DeleteLoadedEntities<TBase, T>(this ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate = null)
        where T : class, TBase
        where TBase : class
    {
        IObjectContextWithBulkOperations context = entitySet.Context as IObjectContextWithBulkOperations;
        if (context == null)
            throw new NotImplementedException();
        if (predicate == null)
            predicate = item => true;
        context.DeleteLoadedEntities(entitySet, predicate);
    }
    public static void Delete<T>(this ObjectSet<T> entitySet, Expression<Func<T, bool>> predicate = null)
        where T : class
    {
        Delete<T, T>(entitySet, predicate);
    }
    public static void DeleteLoadedEntities<T>(this ObjectSet<T> entitySet, Expression<Func<T, bool>> predicate = null)
        where T : class
    {
        DeleteLoadedEntities<T, T>(entitySet, predicate);
    }
}


public interface IObjectContextWithBulkOperations
{
    void Delete<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate)
        where T : class, TBase
        where TBase : class;
    void DeleteLoadedEntities<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate)
        where T : class, TBase
        where TBase : class;
}


public class ObjectContextWithBulkOperations : ObjectContext, IObjectContextWithBulkOperations
{
    public ObjectContextWithBulkOperations(EntityConnection connection)
        : base(connection)
    {
        OnContextCreated();
    }
    public ObjectContextWithBulkOperations(string connectionString)
        : base(connectionString)
    {
        OnContextCreated();
    }
    protected ObjectContextWithBulkOperations(EntityConnection connection, string defaultContainerName)
        : base(connection, defaultContainerName)
    {
        OnContextCreated();
    }
    protected ObjectContextWithBulkOperations(string connectionString, string defaultContainerName)
        : base(connectionString, defaultContainerName)
    {
        OnContextCreated();
    }

    private void OnContextCreated()
    {
        ObjectMaterialized += NorthwindEntities_ObjectMaterialized;
    }

    private List<Action> _bulkDeletedActions;
    private List<Action> BulkDeletedActions
    {
        get
        {
            if (_bulkDeletedActions == null)
                _bulkDeletedActions = new List<Action>();
            return _bulkDeletedActions;
        }
    }

    private List<object> _bulkDeletedEntities;
    public List<object> BulkDeletedEntities
    {
        get
        {
            if (_bulkDeletedEntities == null)
                _bulkDeletedEntities = new List<object>();
            return _bulkDeletedEntities;
        }
    }

    private Dictionary<Type, List<Func<object, bool>>> _bulkDeletedFuncs;
    public Dictionary<Type, List<Func<object, bool>>> BulkDeletedFuncs
    {
        get
        {
            if (_bulkDeletedFuncs == null)
                _bulkDeletedFuncs = new Dictionary<Type, List<Func<object, bool>>>();
            return _bulkDeletedFuncs;
        }
    }

    public void Delete<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate)
        where T : class, TBase
        where TBase : class
    {
        Delete<TBase, T>(entitySet, predicate, true);
    }
    public void DeleteLoadedEntities<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate)
        where T : class, TBase
        where TBase : class
    {
        if ((predicate = CalculatePredicate(entitySet, predicate)) != null)
            Delete<TBase, T>(entitySet, predicate, false);
    }

    private Expression<Func<T, bool>> CalculatePredicate<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> oldPredicate)
        where T : class, TBase
        where TBase : class
    {
        IEnumerable<PropertyInfo> keyMembers = entitySet.EntitySet.ElementType.KeyMembers.Select(km => typeof(T).GetProperty(km.Name)).ToList();
        IEnumerable<T> entitiesEnumerable = ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged)
                                            .Select(ose => ose.Entity)
                                            .OfType<T>();
        ParameterExpression parameter = oldPredicate.Parameters.Single();
        if (!entitiesEnumerable.Any())
            return null;
        return Expression.Lambda<Func<T, bool>>(
            Expression.AndAlso(
                oldPredicate.Body,
                entitiesEnumerable.Select(e =>
                    keyMembers.Select(km =>
                        Expression.Equal(
                            Expression.MakeMemberAccess(parameter, km),
                            Expression.Constant(km.GetValue(e, null))))
                    .Aggregate((accumulate, clause) =>
                        Expression.AndAlso(accumulate, clause)))
                .Aggregate((accumulate, clause) =>
                    Expression.OrElse(accumulate, clause)))
            , oldPredicate.Parameters);
    }

    private void Delete<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> predicate, bool propagateToFutureEntities)
        where TBase : class
        where T : class, TBase
    {
        ObjectQuery<T> objectQuery = (ObjectQuery<T>)entitySet.OfType<T>().Where(predicate);
        string selectSQLQuery = objectQuery.ToTraceString();
        List<KeyValuePair<string, List<string>>> froms = new List<KeyValuePair<string, List<string>>>();
        Match fromMatch = Regex.Match(entitySet.OfType<T>().ToTraceString(), "(FROM|JOIN)[ ]+((\\[[^\\]]+\\]).)*\\[([^\\]]+)\\]");
        List<AssociationType> ssdlAsscociations = MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<AssociationType>().ToList();
        string firstFrom = null;
        while (fromMatch.Success)
        {
            string fromValue = fromMatch.Groups[4].Value;
            if (Regex.IsMatch(selectSQLQuery, string.Format("(FROM|JOIN)[ ]+((\\[[^\\]]+\\]).)*\\[{0}\\]", fromValue)))
            {
                var index = (from ssdlAssociation in ssdlAsscociations
                                where ssdlAssociation.ReferentialConstraints.Any(rc => fromValue == rc.ToProperties.First().DeclaringType.Name)
                                from table in froms.Select((f, i) => new { Table = f, Index = i })
                                where ssdlAssociation.ReferentialConstraints.Any(rc => table.Table.Key == rc.FromProperties.First().DeclaringType.Name)
                                orderby table.Index
                                select new { Index = table.Index, SSDLAssociation = ssdlAssociation, FKs = table.Table }).FirstOrDefault();
                if (index != null)
                    froms.Insert(index.Index, new KeyValuePair<string, List<string>>(fromValue, (from fk in index.FKs.Value
                                                                                                    let referentailConstraint = index.SSDLAssociation.ReferentialConstraints.First(rc => index.FKs.Key == rc.FromProperties.First().DeclaringType.Name)
                                                                                                    select referentailConstraint.ToProperties.ElementAt(referentailConstraint.FromProperties.Select((p, pIndex) => new { p.Name, Index = pIndex }).First(p => p.Name == fk).Index).Name).ToList()));
                else
                {
                    if (firstFrom == null)
                        firstFrom = fromValue;
                    froms.Add(new KeyValuePair<string, List<string>>(fromValue, MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<EntityType>().First(et => et.Name == fromValue).KeyMembers.Select(km => km.Name).ToList()));
                }
            }
            fromMatch = fromMatch.NextMatch();
        }
        StringBuilder delete = new StringBuilder();

        string selectSQLQueryWithoutSelect = selectSQLQuery.Substring(selectSQLQuery.IndexOf("FROM"));
        IEnumerator<EdmMember> keyMembersEnumerator = null;

        if (froms.Count > 1)
        {
            delete.Append("declare @DeleteIds table (");
            StringBuilder keys = new StringBuilder();
            keyMembersEnumerator = MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<EntityType>().
                First(et => et.Name == firstFrom).KeyMembers.ToList().GetEnumerator();
            keyMembersEnumerator.MoveNext();
            for (; ; )
            {
                string keyName = keyMembersEnumerator.Current.Name;
                keys.Append(keyName);
                delete.Append(keyName);
                delete.Append(" ");
                delete.Append(keyMembersEnumerator.Current.TypeUsage.EdmType.Name);
                Facet maxLength = keyMembersEnumerator.Current.TypeUsage.Facets.FirstOrDefault(f => f.Name == "MaxLength");
                if (maxLength != null)
                {
                    delete.Append("(");
                    delete.Append(maxLength.Value);
                    delete.Append(")");
                }
                if (keyMembersEnumerator.MoveNext())
                {
                    keys.Append(", ");
                    delete.Append(", ");
                }
                else
                    break;
            }
            delete.Append(");\n");

            delete.Append("INSERT INTO @DeleteIds SELECT ");
            delete.Append(keys.ToString());
            delete.Append(" ");
            delete.Append(selectSQLQueryWithoutSelect.Replace("@p__linq__", "@p"));
            delete.Append(";\n");
        }

        foreach (KeyValuePair<string, List<string>> from in froms)
        {
            delete.Append("DELETE FROM [");
            delete.Append(from.Key);
            delete.Append("] FROM ");

            if (froms.Count > 1)
            {
                delete.Append("[");
                delete.Append(from.Key);
                delete.Append("]");
                delete.Append("INNER JOIN @deleteIds D ON ");

                keyMembersEnumerator.Reset();
                keyMembersEnumerator.MoveNext();
                int index = 0;
                for (; ; )
                {
                    delete.Append("[");
                    delete.Append(from.Key);
                    delete.Append("].");
                    delete.Append(from.Value[index++]);
                    delete.Append(" = D.");
                    delete.Append(keyMembersEnumerator.Current);

                    if (keyMembersEnumerator.MoveNext())
                        delete.Append(" AND ");
                    else
                        break;
                }
            }
            else
                delete.Append(selectSQLQueryWithoutSelect.Substring(4).TrimStart());

            delete.Append(";\n");
        }

        BulkDeletedActions.Add(() => ExecuteStoreCommand(delete.ToString(), objectQuery.Parameters.Select(p => p.Value).ToArray()));

        Func<T, bool> predicateCompiled = predicate.Compile();
        Func<object, bool> predicateCompiledObject = o =>
        {
            T t = o as T;
            if (t == null)
                return false;
            return predicateCompiled(t);
        };
        if (propagateToFutureEntities)
        {
            List<Func<object, bool>> bulkDeletedFuncs;
            if (BulkDeletedFuncs.TryGetValue(typeof(TBase), out bulkDeletedFuncs))
                bulkDeletedFuncs.Add(predicateCompiledObject);
            else
                BulkDeletedFuncs.Add(typeof(TBase), new List<Func<object, bool>>() { predicateCompiledObject });
        }
        EntityType entityType = MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(T).Name);
        var oneToOneSubEntityTypes = (from np in entityType.NavigationProperties
                                        where np.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One
                                        let otherEntityType = np.ToEndMember.GetEntityType()
                                        let otherNavigationProperty = otherEntityType.NavigationProperties.FirstOrDefault(otherNP => otherNP.RelationshipType == np.RelationshipType)
                                        select new 
                                        {
                                            EntityType = otherEntityType,
                                            ClrType = typeof(T).GetProperty(np.Name).PropertyType,
                                            OtherNavigationPropertyName = otherNavigationProperty == null ? null : otherNavigationProperty.Name,
                                            ReferencialConstraint = ((AssociationType)np.RelationshipType).ReferentialConstraints.FirstOrDefault()
                                        }).ToList();
        foreach (var subEntityTypeLoop in oneToOneSubEntityTypes)
        {
            var subEntityType = subEntityTypeLoop;
            if (subEntityType.OtherNavigationPropertyName != null)
            {
                List<string> entityTypeKeys, subEntityTypeKeys;
                if (subEntityType.ReferencialConstraint.FromProperties.First().DeclaringType == entityType)
                {
                    entityTypeKeys = subEntityType.ReferencialConstraint.FromProperties.Select(p => p.Name).ToList();
                    subEntityTypeKeys = subEntityType.ReferencialConstraint.ToProperties.Select(p => p.Name).ToList();
                }
                else
                {
                    entityTypeKeys = subEntityType.ReferencialConstraint.ToProperties.Select(p => p.Name).ToList();
                    subEntityTypeKeys = subEntityType.ReferencialConstraint.FromProperties.Select(p => p.Name).ToList();
                }
                ParameterExpression entityParameter = Expression.Parameter(typeof(object), "entity");
                ParameterExpression subEntityParameter = Expression.Parameter(typeof(object), "subEntity");
                Func<object, object, bool> associateToBulkEntities =
                    Expression.Lambda<Func<object, object, bool>>(
                        entityTypeKeys.Select((entityTypeKey, keyIndex) =>
                            Expression.Equal(
                                Expression.MakeMemberAccess(
                                    Expression.Convert(
                                        subEntityParameter,
                                        subEntityType.ClrType),
                                    subEntityType.ClrType.GetProperty(subEntityTypeKeys[keyIndex])),
                                Expression.MakeMemberAccess(
                                    Expression.Convert(
                                        entityParameter,
                                        typeof(T)),
                                    typeof(T).GetProperty(entityTypeKey)))).
                        Aggregate((accumulate, keyPredicate) => Expression.AndAlso(accumulate, keyPredicate)),
                        entityParameter,
                        subEntityParameter).
                        Compile();
                Func<object, bool> npPredicate = subE => BulkDeletedEntities.OfType<T>().Any(e => associateToBulkEntities(e, subE));

                List<Func<object, bool>> bulkDeletedFuncs;
                if (BulkDeletedFuncs.TryGetValue(subEntityType.ClrType, out bulkDeletedFuncs))
                    bulkDeletedFuncs.Add(npPredicate);
                else
                    BulkDeletedFuncs.Add(subEntityType.ClrType, new List<Func<object, bool>>() { npPredicate });
            }
        }
        foreach (var entity in ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).
                                Select(ose => new { Entity = ose.Entity as T, ose.State }).
                                Where(e => e.Entity != null && predicateCompiled(e.Entity)))
        {
            if (entity.State != EntityState.Deleted)
                DeleteObjectAndAddThemIntoBulkDeletedEntities(entity.Entity);
            else
            {
                BulkDeletedEntities.Add(entity.Entity);
                foreach (var subEntity in oneToOneSubEntityTypes.
                                            SelectMany(subEntityType =>
                                                ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).
                                                Where(ose => subEntityType.ClrType.IsAssignableFrom(ose.Entity.GetType()) && !BulkDeletedEntities.Contains(ose.Entity))))
                    ApplyBulkDeletedFuncs(subEntity.Entity, subEntity.State);
            }
        }
    }

    private void NorthwindEntities_ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
    {
        ApplyBulkDeletedFuncs(e.Entity, EntityState.Unchanged);
    }

    private void ApplyBulkDeletedFuncs(object entity, EntityState entityState)
    {
        List<Func<object, bool>> bulkDeletedFuncs;
        if (_bulkDeletedFuncs != null)
        {
            Type t = entity.GetType();
            do
            {
                if (BulkDeletedFuncs.TryGetValue(t, out bulkDeletedFuncs))
                    foreach (Func<object, bool> bulkDeletedFunc in bulkDeletedFuncs)
                        if (bulkDeletedFunc(entity))
                        {
                            if (entityState != EntityState.Deleted)
                                DeleteObjectAndAddThemIntoBulkDeletedEntities(entity);
                            else
                                BulkDeletedEntities.Add(entity);
                            return;
                        }
            } while ((t = t.BaseType) != null);
        }
    }

    private void DeleteObjectAndAddThemIntoBulkDeletedEntities(object entity)
    {
        CollectionChangeEventHandler objectStateManagerObjectStateManagerChanged = (sender, e) => BulkDeletedEntities.Add(e.Element);
        ObjectStateManager.ObjectStateManagerChanged += objectStateManagerObjectStateManagerChanged;
        DeleteObject(entity);
        ObjectStateManager.ObjectStateManagerChanged -= objectStateManagerObjectStateManagerChanged;
        BulkDeletedEntities.Add(entity);
    }

    public override int SaveChanges(SaveOptions options)
    {
        int value;
        using (TransactionScope transaction = new TransactionScope())
        {
            if (_bulkDeletedActions != null)
                foreach (Action action in _bulkDeletedActions)
                    action();
            if (_bulkDeletedEntities != null)
                foreach (object entity in _bulkDeletedEntities)
                {
                    ObjectStateEntry ose;
                    if (ObjectStateManager.TryGetObjectStateEntry(entity, out ose))
                        Detach(entity);
                }
            value = base.SaveChanges(options);
            transaction.Complete();
            BulkDeletedActions.Clear();
            BulkDeletedEntities.Clear();
            BulkDeletedFuncs.Clear();
        }
        return value;
    }

    protected override void Dispose(bool disposing)
    {
        base.Dispose(disposing);
        if (disposing)
            ObjectMaterialized -= NorthwindEntities_ObjectMaterialized;
    }
}


 



 



 



Now, I will “try” to explain it.



So a Bulk delete implies only one SQL delete on many rows. It is not supported by EF4 which execute one delete per row.



So my idea is to generate and execute myself the delete SQL command.



I add my Delete methods on my ObjectContextWithBulkOperations which inherits of ObjectConext with the ability to filter the delete only on a subtype and to add a delete condition. I also want to be able to delete only loaded entities (with only one delete command in DB). I have four delete methods for this.



Then, as it’s usable to have the delete directly on the ObjectSet<T> class and so to be able to not mention the generic type parameter, I add four extension method which just call themselves ObjectContextWithBulkOperations methods.



Ok. Now let me explain the most important class: ObjectContextWithBulkOperations.



When the delete method is called, we have to:



  • complete predicate to add a condition on keys if bulk delete is only on loaded entities
  • determine the SQL delete command
  • register the delete command to execute it only on the SaveChanges method
  • set the already loaded entities to deleted state
  • register the way to determine if the entities will be deleted. Like this, we will be available to delete automatically an entity loaded after the Delete call  (before SaveChanges) if we don’t apply my delete to only loaded entities.


When we call the SaveChanges method, we have to:



  • start a transaction
  • execute all my SQL delete commands
  • detach all entities deleted by one of the delete commands
  • call framework SaveChanges
  • close my transaction.


Complete predicate to add a condition on keys if bulk delete is only on loaded entities



I wanted to use Contains method but as the entity key can include several properties, I can’t. So I use an Or logic with And on all key properties.



    private Expression<Func<T, bool>> CalculatePredicate<TBase, T>(ObjectSet<TBase> entitySet, Expression<Func<T, bool>> oldPredicate)
        where T : class, TBase
        where TBase : class
    {
        IEnumerable<PropertyInfo> keyMembers = entitySet.EntitySet.ElementType.KeyMembers.Select(km => typeof(T).GetProperty(km.Name)).ToList();
        IEnumerable<T> entitiesEnumerable = ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged)
                                            .Select(ose => ose.Entity)
                                            .OfType<T>();
        ParameterExpression parameter = oldPredicate.Parameters.Single();
        if (!entitiesEnumerable.Any())
            return null;
        return Expression.Lambda<Func<T, bool>>(
            Expression.AndAlso(
                oldPredicate.Body,
                entitiesEnumerable.Select(e =>
                    keyMembers.Select(km =>
                        Expression.Equal(
                            Expression.MakeMemberAccess(parameter, km),
                            Expression.Constant(km.GetValue(e, null))))
                    .Aggregate((accumulate, clause) =>
                        Expression.AndAlso(accumulate, clause)))
                .Aggregate((accumulate, clause) =>
                    Expression.OrElse(accumulate, clause)))
            , oldPredicate.Parameters);
    }


 



Determine the SQL delete command:



The most important difficulty here is the fact that with EF4 we still don’t have mapping metadata at runtime. So my idea is to parse the generated select SQL query from the ObjectSet to determine tables.



With several mappings scenarios (TPT, TPC, Vertical Entity Splitting, Horizontal Entity Splitting), an EntitySet is mapped on multiple tables. So I have to execute multiple delete queries in each table in the right order to avoid FK constraint violation exception.



How do I parse select SQL query?



First, I will parse the entitySet.OfType<T> generated select SQL query without condition to avoid impact of the where clause to determine tables. Then I will verify if this table is still present on the select generated query with the where clause. Indeed with Horizontal Entity Splitting or TPC, the Where clause can eliminate some tables.



If I have more than one query, I have to generate several SQL delete commands (in the right order). In each mapping scenario with multiple tables, all tables “have the same PK type”. So I will insert into a temporary table pks of the rows which will be deleted and then I can execute all my SQL delete commands with an inner join with my temporary table. This can break compatibility with DB providers other than SQL Server.

          ObjectQuery<T> objectQuery = (ObjectQuery<T>)entitySet.OfType<T>().Where(predicate);


        string selectSQLQuery = objectQuery.ToTraceString();


        List<KeyValuePair<string, List<string>>> froms = new List<KeyValuePair<string, List<string>>>();


        Match fromMatch = Regex.Match(entitySet.OfType<T>().ToTraceString(), "(FROM|JOIN)[ ]+((\\[[^\\]]+\\]).)*\\[([^\\]]+)\\]");


        List<AssociationType> ssdlAsscociations = MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<AssociationType>().ToList();


        string firstFrom = null;


        while (fromMatch.Success)


        {


            string fromValue = fromMatch.Groups[4].Value;


            if (Regex.IsMatch(selectSQLQuery, string.Format("(FROM|JOIN)[ ]+((\\[[^\\]]+\\]).)*\\[{0}\\]", fromValue)))


            {


                var index = (from ssdlAssociation in ssdlAsscociations


                                where ssdlAssociation.ReferentialConstraints.Any(rc => fromValue == rc.ToProperties.First().DeclaringType.Name)


                                from table in froms.Select((f, i) => new { Table = f, Index = i })


                                where ssdlAssociation.ReferentialConstraints.Any(rc => table.Table.Key == rc.FromProperties.First().DeclaringType.Name)


                                orderby table.Index


                                select new { Index = table.Index, SSDLAssociation = ssdlAssociation, FKs = table.Table }).FirstOrDefault();


                if (index != null)


                    froms.Insert(index.Index, new KeyValuePair<string, List<string>>(fromValue, (from fk in index.FKs.Value


                                                                                                    let referentailConstraint = index.SSDLAssociation.ReferentialConstraints.First(rc => index.FKs.Key == rc.FromProperties.First().DeclaringType.Name)


                                                                                                    select referentailConstraint.ToProperties.ElementAt(referentailConstraint.FromProperties.Select((p, pIndex) => new { p.Name, Index = pIndex }).First(p => p.Name == fk).Index).Name).ToList()));


                else


                {


                    if (firstFrom == null)


                        firstFrom = fromValue;


                    froms.Add(new KeyValuePair<string, List<string>>(fromValue, MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<EntityType>().First(et => et.Name == fromValue).KeyMembers.Select(km => km.Name).ToList()));


                }


            }


            fromMatch = fromMatch.NextMatch();


        }


        StringBuilder delete = new StringBuilder();





        string selectSQLQueryWithoutSelect = selectSQLQuery.Substring(selectSQLQuery.IndexOf("FROM"));


        IEnumerator<EdmMember> keyMembersEnumerator = null;





        if (froms.Count > 1)


        {


            delete.Append("declare @DeleteIds table (");


            StringBuilder keys = new StringBuilder();


            keyMembersEnumerator = MetadataWorkspace.GetItems(DataSpace.SSpace).OfType<EntityType>().


                First(et => et.Name == firstFrom).KeyMembers.ToList().GetEnumerator();


            keyMembersEnumerator.MoveNext();


            for (; ; )


            {


                string keyName = keyMembersEnumerator.Current.Name;


                keys.Append(keyName);


                delete.Append(keyName);


                delete.Append(" ");


                delete.Append(keyMembersEnumerator.Current.TypeUsage.EdmType.Name);


                Facet maxLength = keyMembersEnumerator.Current.TypeUsage.Facets.FirstOrDefault(f => f.Name == "MaxLength");


                if (maxLength != null)


                {


                    delete.Append("(");


                    delete.Append(maxLength.Value);


                    delete.Append(")");


                }


                if (keyMembersEnumerator.MoveNext())


                {


                    keys.Append(", ");


                    delete.Append(", ");


                }


                else


                    break;


            }


            delete.Append(");\n");





            delete.Append("INSERT INTO @DeleteIds SELECT ");


            delete.Append(keys.ToString());


            delete.Append(" ");


            delete.Append(selectSQLQueryWithoutSelect.Replace("@p__linq__", "@p"));


            delete.Append(";\n");


        }





        foreach (KeyValuePair<string, List<string>> from in froms)


        {


            delete.Append("DELETE FROM [");


            delete.Append(from.Key);


            delete.Append("] FROM ");





            if (froms.Count > 1)


            {


                delete.Append("[");


                delete.Append(from.Key);


                delete.Append("]");


                delete.Append("INNER JOIN @deleteIds D ON ");





                keyMembersEnumerator.Reset();


                keyMembersEnumerator.MoveNext();


                int index = 0;


                for (; ; )


                {


                    delete.Append("[");


                    delete.Append(from.Key);


                    delete.Append("].");


                    delete.Append(from.Value[index++]);


                    delete.Append(" = D.");


                    delete.Append(keyMembersEnumerator.Current);





                    if (keyMembersEnumerator.MoveNext())


                        delete.Append(" AND ");


                    else


                        break;


                }


            }


            else


                delete.Append(selectSQLQueryWithoutSelect.Substring(4).TrimStart());





            delete.Append(";\n");


        }






Register the delete command to execute it only on the SaveChanges method



Here, I use BulkDeletedActions list of actions which will execute SQL delete commands when it will be called

            BulkDeletedActions.Add(() => ExecuteStoreCommand(delete.ToString(), objectQuery.Parameters.Select(p => p.Value).ToArray()));






Set the already loaded entities to deleted state



To determine deleted entities, I compile the bulk delete predicate Expression to have a Func and to be able to execute it on my entities present in the cache.

            foreach (var entity in ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).


                                Select(ose => new { Entity = ose.Entity as T, ose.State }).


                                Where(e => e.Entity != null && predicateCompiled(e.Entity)))


        {


            if (entity.State != EntityState.Deleted)


                DeleteObjectAndAddThemIntoBulkDeletedEntities(entity.Entity);




With Table Splitting, I can have multiple entity types mapped on the same table. So I have to delete all of them. Because I defined one to one property in my CSDL, these “sub-entities” will be automatically deleted and I just have to add them in BulkDeletedEntities (to detach them before the SaveChanges).



    private void DeleteObjectAndAddThemIntoBulkDeletedEntities(object entity)


    {


        CollectionChangeEventHandler objectStateManagerObjectStateManagerChanged = (sender, e) => BulkDeletedEntities.Add(e.Element);


        ObjectStateManager.ObjectStateManagerChanged += objectStateManagerObjectStateManagerChanged;


        DeleteObject(entity);


        ObjectStateManager.ObjectStateManagerChanged -= objectStateManagerObjectStateManagerChanged;


        BulkDeletedEntities.Add(entity);


    }




The problem here is the fact that the entities can already been deleted. In this case, I have a special process:



            else


            {


                BulkDeletedEntities.Add(entity.Entity);


                foreach (var subEntity in oneToOneSubEntityTypes.


                                            SelectMany(subEntityType =>


                                                ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).


                                                Where(ose => subEntityType.ClrType.IsAssignableFrom(ose.Entity.GetType()) && !BulkDeletedEntities.Contains(ose.Entity))))


                    ApplyBulkDeletedFuncs(subEntity.Entity, subEntity.State);


            }


        }




with this code to get one to one associated entities



        EntityType entityType = MetadataWorkspace.GetItems(DataSpace.CSpace).OfType<EntityType>().First(et => et.Name == typeof(T).Name);


        var oneToOneSubEntityTypes = (from np in entityType.NavigationProperties


                                        where np.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One && np.ToEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One


                                        let otherEntityType = np.ToEndMember.GetEntityType()


                                        let otherNavigationProperty = otherEntityType.NavigationProperties.FirstOrDefault(otherNP => otherNP.RelationshipType == np.RelationshipType)


                                        select new 
                                        { 
                                            EntityType = otherEntityType, 
                                            ClrType = typeof(T).GetProperty(np.Name).PropertyType, 
                                            OtherNavigationPropertyName = otherNavigationProperty == null ? null : otherNavigationProperty.Name, 
                                            ReferencialConstraint = ((AssociationType)np.RelationshipType).ReferentialConstraints.FirstOrDefault() 
                                        }).ToList();






Register the way to determine if the entities will be deleted



My idea here is to use my Func and to run it on entity materialization. For this, I will save all actions per type (only if I don’t want to run my Deleted only on loaded entities):



            List<Func<object, bool>> bulkDeletedFuncs;
            if (BulkDeletedFuncs.TryGetValue(typeof(TBase), out bulkDeletedFuncs))
                bulkDeletedFuncs.Add(predicateCompiledObject);
            else
                BulkDeletedFuncs.Add(typeof(TBase), new List<Func<object, bool>>() { predicateCompiledObject });


 



But Table Splitting is again a problem here. Indeed, imagine that we load e1. Then we call e1s.Delete(). Then, we load e2 which has the same key as e1. I have to set e2 to deleted state myself and to add e2 in BulkDeletedEntities.



To do it, when I delete an entity (with bulk delete), I also register func for one to one sub entity in BulkDeletedFuncs. // Note that I use an Expression that I compile to improve performance compared to using Reflection.



        foreach (var subEntityTypeLoop in oneToOneSubEntityTypes)
        {
            var subEntityType = subEntityTypeLoop;
            if (subEntityType.OtherNavigationPropertyName != null)
            {
                List<string> entityTypeKeys, subEntityTypeKeys;
                if (subEntityType.ReferencialConstraint.FromProperties.First().DeclaringType == entityType)
                {
                    entityTypeKeys = subEntityType.ReferencialConstraint.FromProperties.Select(p => p.Name).ToList();
                    subEntityTypeKeys = subEntityType.ReferencialConstraint.ToProperties.Select(p => p.Name).ToList();
                }
                else
                {
                    entityTypeKeys = subEntityType.ReferencialConstraint.ToProperties.Select(p => p.Name).ToList();
                    subEntityTypeKeys = subEntityType.ReferencialConstraint.FromProperties.Select(p => p.Name).ToList();
                }
                ParameterExpression entityParameter = Expression.Parameter(typeof(object), "entity");
                ParameterExpression subEntityParameter = Expression.Parameter(typeof(object), "subEntity");
                Func<object, object, bool> associateToBulkEntities =
                    Expression.Lambda<Func<object, object, bool>>(
                        entityTypeKeys.Select((entityTypeKey, keyIndex) =>
                            Expression.Equal(
                                Expression.MakeMemberAccess(
                                    Expression.Convert(
                                        subEntityParameter,
                                        subEntityType.ClrType),
                                    subEntityType.ClrType.GetProperty(subEntityTypeKeys[keyIndex])),
                                Expression.MakeMemberAccess(
                                    Expression.Convert(
                                        entityParameter,
                                        typeof(T)),
                                    typeof(T).GetProperty(entityTypeKey)))).
                        Aggregate((accumulate, keyPredicate) => Expression.AndAlso(accumulate, keyPredicate)),
                        entityParameter,
                        subEntityParameter).
                        Compile();
                Func<object, bool> npPredicate = subE => BulkDeletedEntities.OfType<T>().Any(e => associateToBulkEntities(e, subE));

                List<Func<object, bool>> bulkDeletedFuncs;
                if (BulkDeletedFuncs.TryGetValue(subEntityType.ClrType, out bulkDeletedFuncs))
                    bulkDeletedFuncs.Add(npPredicate);
                else
                    BulkDeletedFuncs.Add(subEntityType.ClrType, new List<Func<object, bool>>() { npPredicate });
            }
        }


Then, when an entity is materialized, I will check if it should be in deleted state.



    private void NorthwindEntities_ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
    {
        ApplyBulkDeletedFuncs(e.Entity, EntityState.Unchanged);
    }

    private void ApplyBulkDeletedFuncs(object entity, EntityState entityState)
    {
        List<Func<object, bool>> bulkDeletedFuncs;
        if (_bulkDeletedFuncs != null)
        {
            Type t = entity.GetType();
            do
            {
                if (BulkDeletedFuncs.TryGetValue(t, out bulkDeletedFuncs))
                    foreach (Func<object, bool> bulkDeletedFunc in bulkDeletedFuncs)
                        if (bulkDeletedFunc(entity))
                        {
                            if (entityState != EntityState.Deleted)
                                DeleteObjectAndAddThemIntoBulkDeletedEntities(entity);
                            else
                                BulkDeletedEntities.Add(entity);
                            return;
                        }
            } while ((t = t.BaseType) != null);
        }
    }




Execute all my SQL delete commands



Here, I just have to iterate on BulkDeletedActions and to call each of them.



            if (_bulkDeletedActions != null)
                foreach (Action action in _bulkDeletedActions)
                    action();




Detach all entities deleted by one of the delete commands



As previous actions delete some DB row, I have to detach associated deleted entities in order to avoid a Delete which returns 0 and so which throws an exception in SaveChanges.



            if (_bulkDeletedEntities != null)
                foreach (object entity in _bulkDeletedEntities)
                {
                    ObjectStateEntry ose;
                    if (ObjectStateManager.TryGetObjectStateEntry(entity, out ose))
                        Detach(entity);
                }




Call framework SaveChanges



            value = base.SaveChanges(options);


 



Then I close the transaction and I reset my 3 lists / dictionaries.



            transaction.Complete();
            BulkDeletedActions.Clear();
            BulkDeletedEntities.Clear();
            BulkDeletedFuncs.Clear();


 



Here we are folks!



I think that I cover all mapping scenarios in this version.



I now have an EF4 Bulk Delete implementation for SQL Server. //It would be very easy to change this code for other providers.

This entry was posted in 7671, 7674, 7675, 9104. Bookmark the permalink.

4 Responses to Bulk Delete v3

  1. Herman says:

    Thanks for the post! I tried this but when I try to instantiate the ObjectContextWithBulkOperations I get the following Exception:

    “Unable to load the specified metadata resource.”
    at System.Data.Metadata.Edm.MetadataArtifactLoaderCompositeResource.LoadResources(String assemblyName, String resourceName, ICollection`1 uriRegistry, MetadataArtifactAssemblyResolver resolver)
    at System.Data.Metadata.Edm.MetadataArtifactLoaderCompositeResource.CreateResourceLoader(String path, ExtensionCheck extensionCheck, String validExtension, ICollection`1 uriRegistry, MetadataArtifactAssemblyResolver resolver)
    at System.Data.Metadata.Edm.MetadataArtifactLoader.Create(String path, ExtensionCheck extensionCheck, String validExtension, ICollection`1 uriRegistry, MetadataArtifactAssemblyResolver resolver)
    at System.Data.Metadata.Edm.MetadataCache.SplitPaths(String paths)
    at System.Data.Common.Utils.Memoizer`2.<>c__DisplayClass2.b__0()
    at System.Data.Common.Utils.Memoizer`2.Result.GetValue()
    at System.Data.Common.Utils.Memoizer`2.Evaluate(TArg arg)
    at System.Data.EntityClient.EntityConnection.GetMetadataWorkspace(Boolean initializeAllCollections)
    at System.Data.Objects.ObjectContext.RetrieveMetadataWorkspaceFromConnection()
    at System.Data.Objects.ObjectContext..ctor(EntityConnection connection, Boolean isConnectionConstructor)
    at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName)
    at System.Data.Objects.ObjectContextWithBulkOperations..ctor(String connectionString, String defaultContainerName) in E:\Sevenf\Customers\HealthMonitor\SVN\MediSim.Net\app\MediSim.Net\MediSim.Data\BulkOperations\ObjectContextWithBulkOperations .cs:line 30

  2. Matthieu MEZIL says:

    “Unable to load the specified metadata resource” is probably a problem on your connection string. Did you try to only instanciate your ObjectContext?

  3. yisman says:

    hi
    i made a special class library for this code
    but i get a compilation error

    ‘System.Data.Metadata.Edm.ReadOnlyMetadataCollection‘ does not contain a definition for ‘Select’ and no extension method ‘Select’ accepting a first argument of type ‘System.Data.Metadata.Edm.ReadOnlyMetadataCollection‘ could be found (are you missing a using directive or an assembly reference?)

    theres are my usings
    using System.Data.Objects;
    using System;
    using System.Linq.Expressions;
    using System.Data.EntityClient;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Data;
    using System.Text.RegularExpressions;
    using System.Data.Metadata.Edm;
    using System.Text;

    p.s im new to C# (though pro in vb.net)

    thank you very very much

  4. Matthieu MEZIL says:

    Add “using System.Linq;”

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>