.net - 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"));