Bulk Delete with EF4

Update: I made a new version here.


EF4 doesn’t support neither Bulk Delete nor Bulk Update.


So to delete all records in a table T, we first have to load every entity mapped on this table, to delete all of them and to SaveChanges (=> n delete queries in DB). Not great! :(


How to do it better?


In this case, Microsoft advises to use stored procedure but I think that it sucks! Indeed, with SP, it is immediately executed (not on context.SaveChanges() and so not on context.SaveChanges transaction) and it does not synchronize context cache (ObjectStateManager).


I realized a proof of concept with Delete to fix this.


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

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

Then, I create a new ObjectContext class and I used T4 in my project to inherit of my ObjectContext  which itself inherits of System.Data.Entity.ObjectContext.


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
    {
        ObjectQuery<T> objectQuery = (ObjectQuery<T>)entitySet.OfType<T>().Where(predicate);
        string selectSQLQuery = objectQuery.ToTraceString();
        string from = Regex.Match(selectSQLQuery, "FROM [\\[A-Za-z0-9\\] .]+\\] AS").Value;
        from = from.Substring(0, from.Length - 3);
        IEnumerator<EdmMember> keyMembersEnumerator = entitySet.EntitySet.ElementType.KeyMembers.GetEnumerator();
        StringBuilder joinClause = new StringBuilder();
        keyMembersEnumerator.MoveNext();
        for (; ; )
        {
            joinClause.Append("MMExtent.");
            joinClause.Append(keyMembersEnumerator.Current);
            joinClause.Append(" = MMExtent2.");
            joinClause.Append(keyMembersEnumerator.Current);

            if (keyMembersEnumerator.MoveNext())
                joinClause.Append(" AND ");
            else
                break;
        }
        BulkDeletedActions.Add(() => ExecuteStoreCommand(string.Format("DELETE MMExtent {0} AS MMExtent INNER JOIN ({1}) AS MMExtent2 ON {2}", from, objectQuery.ToTraceString().Replace("@p__linq__", "@p"), joinClause.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);
                };
        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 });
        foreach (var entity in ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified | EntityState.Unchanged).Select(ose => ose.Entity).OfType<T>().Where(e => predicateCompiled(e)))
        {
            DeleteObject(entity);
            BulkDeletedEntities.Add(entity);
        }
    }


    private void NorthwindEntities_ObjectMaterialized(object sender, ObjectMaterializedEventArgs e)
    {
        List<Func<object, bool>> bulkDeletedFuncs;
        if (_bulkDeletedFuncs != null)
        {
            Type t = e.Entity.GetType();
            do
            {
                if (BulkDeletedFuncs.TryGetValue(t, out bulkDeletedFuncs))
                    foreach (Func<object, bool> bulkDeletedFunc in bulkDeletedFuncs)
                        if (bulkDeletedFunc(e.Entity))
                        {
                            ObjectStateManager.GetObjectStateEntry(e.Entity).Delete();
                            BulkDeletedEntities.Add(e.Entity);
                            return;
                        }
            } while ((t = t.BaseType) != null);
        }
    }




    public override int SaveChanges(SaveOptions options)
    {
        int value;
        using (TransactionScope transaction = new TransactionScope())
        {
            if (_bulkDeletedEntities != null)
                foreach (object entity in _bulkDeletedEntities)
                {
                    ObjectStateEntry ose;
                    if (ObjectStateManager.TryGetObjectStateEntry(entity, out ose))
                        Detach(entity);
                }
            bool acceptChanges = (options & SaveOptions.AcceptAllChangesAfterSave) == SaveOptions.AcceptAllChangesAfterSave;
            if (acceptChanges)
                options ^= SaveOptions.AcceptAllChangesAfterSave;
            value = base.SaveChanges(options);
            if (_bulkDeletedActions != null)
                foreach (Action action in _bulkDeletedActions)
                    action();
            transaction.Complete();
            if (acceptChanges)
                AcceptAllChanges();
        }
        return value;
    }


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

Here we are folks! We now have Bulk delete with EF4! [:)]


Now, for example, you can write something like this:


context.Categories.Delete(c => true);

context.Categories.Delete(c => c.CategoryName.StartsWith("MM"));

context.Categories.Delete(c => c.CategoryName.StartsWith(test));

context.Categories.Delete(c => !c.Products.Any());

context.Order_Details.Delete(c => c.Quantity > 10000000);

The only problem we still have with this solution is when we have a logical order in deletion. 
This entry was posted in 7671, 7674. Bookmark the permalink.

3 Responses to Bulk Delete with EF4

  1. Matthieu MEZIL says:

    This solution works with SQL Server. Not sure with other providers.

  2. Bulk UPDATE? says:

    Hey bud,
    you mentioned “In my previous post , I showed you how to do Bulk Update with EF4″ – but i can’t seem to find it anywhere in you blog. can you give me a link please? sorry for being thick :(

  3. Matthieu MEZIL says:

    This is an automatic comment because I refer this post on another one.

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>