EF Code First Delete Batch From IQueryable<T>?

Question

I know this is possible in LINQ-to-SQL, and I've seen bits and pieces that lead me to believe it's possible in EF. Is there an extension out there that can do something like this:

var peopleQuery = Context.People.Where(p => p.Name == "Jim");

peopleQuery.DeleteBatch();

Where DeleteBatch just picks apart the peopleQuery and creates a single SQL statement to delete all the appropriate records, then executes the query directly instead of marking all those entities for deletion and having it do them one by one. I thought I found something like that in the code below, but it fails immediately because instance can't be casted to ObjectSet. Does anyone know how to fix this up to work with EF Code First? Or know of anywhere that has an example of this being done?

public static IQueryable<T> DeleteBatch<T>(this IQueryable<T> instance) where T : class
{
    ObjectSet<T> query = instance as ObjectSet<T>;
    ObjectContext context = query.Context;

    string sqlClause = GetClause<T>(instance);
    context.ExecuteStoreCommand("DELETE {0}", sqlClause);

    return instance;
}

public static string GetClause<T>(this IQueryable<T> clause) where T : class
{
    string snippet = "FROM [dbo].[";

    string sql = ((ObjectQuery<T>)clause).ToTraceString();
    string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));

    sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
    sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");

    return sqlFirstPart;
}

Answer 1

Entity framework doesn't support batch operations. I like the way how the code solves the problem but even it does exactly what you want (but for ObjectContext API) it is a wrong solution.

Why is it wrong solution?

It works only in some cases. It will definitely not work in any advanced mapping solution where entity is mapped to multiple tables (entity splitting, TPT inheritance). I almost sure that you can find another situations where it will not work due to complexity of the query.

It keeps context and database inconsistent. This is a problem of any SQL executed against DB but in this case the SQL is hidden and another programmer using your code can miss it. If you delete any record which is in the same time loaded to the context instance, the entity will not be marked as deleted and removed from context (unless you add that code to your DeleteBatch method - this will be especially complicated if deleted record actually maps to multiple entities (table splitting)).

The most important problem is modification of EF generated SQL query and assumptions you are doing on that query. You are expecting that EF will name the first table used in the query as Extent1. Yes it really uses that name now but it is internal EF implementation. It can change in any minor update of EF. Building custom logic around internals of any API is considered as a bad practice.

As a result you already have to work with query on SQL level so you can call the SQL query directly as @mreyeros showed and avoid risks in this solution. You will have to deal with real names of tables and columns but that is something you can control (your mapping can define them).

If you don't consider these risks as significant you can make small changes to the code to make it work in DbContext API:

public static class DbContextExtensions
{
    public static void DeleteBatch<T>(this DbContext context, IQueryable<T> query) where T : class
    {
        string sqlClause = GetClause<T>(query);
        context.Database.ExecuteSqlCommand(String.Format("DELETE {0}", sqlClause));
    }

    private static string GetClause<T>(IQueryable<T> clause) where T : class
    {
        string snippet = "FROM [dbo].[";

        string sql = clause.ToString();
        string sqlFirstPart = sql.Substring(sql.IndexOf(snippet));

        sqlFirstPart = sqlFirstPart.Replace("AS [Extent1]", "");
        sqlFirstPart = sqlFirstPart.Replace("[Extent1].", "");

        return sqlFirstPart;
    }
}

Now you will call batch delete this way:

context.DeleteBatch(context.People.Where(p => p.Name == "Jim"));

Answer 2

I do not believe that batch operations, like delete are supported yet by EF. You could execute a raw query:

 context.Database.ExecuteSqlCommand("delete from dbo.tbl_Users where isActive = 0"); 

Answer 3

In case anyone else is looking for this functionality, I've used some of Ladislav's comments to improve on his example. Like he said, with the original solution, when you call SaveChanges(), if the context was already tracking one of the entities you deleted it'll call it's own delete. This doesn't modify any records, and EF considers it a concurrency issue and throws an exception. The method below is slower than the original since it has to first query for the items to delete, but it won't write a single delete query for each deleted entity which is the real performance benefit. It detaches all the entities that were queried, so if any of them were already tracked it will know not to delete them anymore.

public static void DeleteBatch<T>(this DbContext context, IQueryable<T> query) where T : LcmpTableBase
{
    IEnumerable<T> toDelete = query.ToList();

    context.Database.ExecuteSqlCommand(GetDeleteCommand(query));

    var toRemove = context.ChangeTracker.Entries<T>().Where(t => t.State == EntityState.Deleted).ToList();

    foreach (var t in toRemove)
        t.State = EntityState.Detached;
}

I also changed up this part to use a regular expression since I found that there was an undetermined amount of whitespace near the FROM portion. I also left "[Extent1]" in there because the DELETE query written in the original way couldn't handle queries with INNER JOINS:

public static string GetDeleteCommand<T>(this IQueryable<T> clause) where T : class
{
    string sql = clause.ToString();

    Match match = Regex.Match(sql, @"FROM\s*\[dbo\].", RegexOptions.IgnoreCase);

    return string.Format("DELETE [Extent1] {0}", sql.Substring(match.Index));
}