实体框架中的批量删除

Question

我想使用linq从表中批量删除记录。有一篇文章描述了如何做到这一点：Bulk-deleting in LINQ to Entities

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

query.Delete();

但是函数"Delete“并不存在于我的var变量中。

此外，在我的上下文中不存在函数"SubmitChanges“。

Answer 1

lets you do batch deletes and updates中有一个有趣的NuGet包

Answer 2

Entity Framework中当前没有支持的批量删除。这实际上是codeplex上正在讨论的特性之一，现在EF是开源的。

EntityFramework.Extended提供了批量删除支持(你可以在nuget中找到)，但是我的经验是它有一些性能问题。

Answer 3

此代码向任何DbContext添加一个简单的扩展方法，该方法将批量删除您提供的实体框架查询中引用的任何表中的所有数据。它的工作方式是提取查询中涉及的所有表名，并尝试通过发出"DELETE FROM tablename“SQL查询来删除数据，这在大多数类型的数据库中都很常见。

要使用，只需执行以下操作：

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

这将删除链接到Things实体存储的表中的所有内容。

代码：

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;

        }
    }
}