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.