Mass removal in essence

Question

I would like you to delete records from a table using linq. There is a message that describes how to do this: Mass removal in LINQ for objects

var query = from c in ctx.Customers where c.SalesPerson.Email == "..." select c; query.Delete(); 

But the "Delete" function does not exist in my var variable.
Also, the "SubmitChanges" function does not exist in my context.

Answer 1

There is an interesting NuGet package that allows you to perform batch deletions and updates :

Answer 2

There is no supported bulk deletion in the Entity Framework. Its actually one of the features discussed on Codeplex, now EF is open source.

EntityFramework.Extended provides support for batch removal (you can find this in nuget), however my experience is that it has some performance issues.

Answer 3

This code adds a simple extension method to any DbContext that will massively delete all the data in any table referenced in the entity framework query that you provide. It works by simply extracting all the names of the tables involved in the query, and tries to delete the data by sending the SQL query "DELETE FROM tablename", which is common to most types of databases.

To use, simply do the following:

 myContext.BulkDelete(x => x.Things); 

which will delete everything in the table associated with the Things object store.

The code:

 using System.Linq; using System.Text.RegularExpressions; namespace System.Data.Entity { public static class DbContextExtensions { /// <summary> /// Efficiently deletes all data from any database tables used by the specified entity framework query. /// </summary> /// <typeparam name="TContext">The DbContext Type on which to perform the delete.</typeparam> /// <typeparam name="TEntity">The Entity Type to which the query resolves.</typeparam> /// <param name="ctx">The DbContext on which to perform the delete.</param> /// <param name="deleteQuery">The query that references the tables you want to delete.</param> public static void BulkDelete<TContext, TEntity>(this TContext ctx, Func<TContext, IQueryable<TEntity>> deleteQuery) where TContext : DbContext { var findTables = new Regex(@"(?:FROM|JOIN)\s+(\[\w+\]\.\[\w+\])\s+AS"); var qry = deleteQuery(ctx).ToString(); // Get list of all tables mentioned in the query var tables = findTables.Matches(qry).Cast<Match>().Select(m => m.Result("$1")).Distinct().ToList(); // Loop through all the tables, attempting to delete each one in turn var max = 30; var exception = (Exception)null; while (tables.Any() && max-- > 0) { // Get the next table var table = tables.First(); try { // Attempt the delete ctx.Database.ExecuteSqlCommand(string.Format("DELETE FROM {0}", table)); // Success, so remove table from the list tables.Remove(table); } catch (Exception ex) { // Error, probably due to dependent constraint, save exception for later if needed. exception = ex; // Push the table to the back of the queue tables.Remove(table); tables.Add(table); } } // Error error has occurred, and cannot be resolved by deleting in a different // order, then rethrow the exception and give up. if (max <= 0 && exception != null) throw exception; } } } 

Answer 4

I do it as if it is working fine. Please advise if there is a reason why this is bad practice.

  var customersToDelete = await ctx.Customers.Where(c => c.Email == "...").ToListAsync(); foreach (var customerToDelete in customersToDelete) { ctx.Customers.Remove(customerToDelete); } await ctx.SaveChangesAsync();