How do I delete multiple rows in Entity Framework (without foreach)

EntityFramework 6 has made this a bit easier with .RemoveRange().

Example:

db.People.RemoveRange(db.People.Where(x => x.State == "CA"));
db.SaveChanges();

using (var context = new DatabaseEntities())
{
    context.ExecuteStoreCommand("DELETE FROM YOURTABLE WHERE CustomerID = {0}", customerId);
}

this is as good as it gets, right? I can abstract it with an extension method or helper, but somewhere we're still going to be doing a foreach, right?

Well, yes, except you can make it into a two-liner:

context.Widgets.Where(w => w.WidgetId == widgetId)
               .ToList().ForEach(context.Widgets.DeleteObject);
context.SaveChanges();

I know it's quite late but in case someone needs a simple solution, the cool thing is you can also add the where clause with it:

public static void DeleteWhere<T>(this DbContext db, Expression<Func<T, bool>> filter) where T : class
{
    string selectSql = db.Set<T>().Where(filter).ToString();
    string fromWhere = selectSql.Substring(selectSql.IndexOf("FROM"));
    string deleteSql = "DELETE [Extent1] " + fromWhere;
    db.Database.ExecuteSqlCommand(deleteSql);
}

Note: just tested with MSSQL2008.

Update:

The solution above won't work when EF generates sql statement with parameters, so here's the update for EF5:

public static void DeleteWhere<T>(this DbContext db, Expression<Func<T, bool>> filter) where T : class
{
    var query = db.Set<T>().Where(filter);

    string selectSql = query.ToString();
    string deleteSql = "DELETE [Extent1] " + selectSql.Substring(selectSql.IndexOf("FROM"));

    var internalQuery = query.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance).Where(field => field.Name == "_internalQuery").Select(field => field.GetValue(query)).First();
    var objectQuery = internalQuery.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Instance).Where(field => field.Name == "_objectQuery").Select(field => field.GetValue(internalQuery)).First() as ObjectQuery;
    var parameters = objectQuery.Parameters.Select(p => new SqlParameter(p.Name, p.Value)).ToArray();

    db.Database.ExecuteSqlCommand(deleteSql, parameters);
}

It requires a little bit of reflection but works well.

If you don't want to execute SQL directly calling DeleteObject in a loop is the best you can do today.

However you can execute SQL and still make it completely general purpose via an extension method, using the approach I describe here.

Although that answer was for 3.5. For 4.0 I would probably use the new ExecuteStoreCommand API under the hood, instead of dropping down to the StoreConnection.

How do I delete multiple rows in Entity Framework (without foreach)

Related

Recent Posts