如何在实体框架中删除多个行(不使用foreach)

I'm deleting several items from a table using Entity Framework. There isn't a foreign key / parent object so I can't handle this with OnDeleteCascade.

我正在使用实体框架从表中删除几个项目。这里没有外键/父对象，所以我无法处理OnDeleteCascade。

Right now I'm doing this:

现在我这样做:

var widgets = context.Widgets
    .Where(w => w.WidgetId == widgetId);

foreach (Widget widget in widgets)
{
    context.Widgets.DeleteObject(widget);
}
context.SaveChanges();

It works but the foreach bugs me. I'm using EF4 but I don't want to execute SQL. I just want to make sure I'm not missing anything - 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?

它很有效，但是foreach让我很讨厌。我正在使用EF4，但是我不想执行SQL。我只是想确保我没有遗漏任何东西-这是最好的，对吧?我可以用扩展方法或者助手来抽象它，但是在某个地方我们仍然要做一个foreach，对吧?

18 个解决方案

#1

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

如果您不想在循环中执行SQL直接调用DeleteObject，那么您今天就能做到最好。

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

但是，您可以执行SQL，并且仍然可以使用我在这里描述的方法，通过扩展方法使其完全通用。

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.

虽然答案是3。5。对于4.0，我可能会在外壳下使用新的ExecuteStoreCommand API，而不是切换到StoreConnection。

#2

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

EntityFramework 6使用. removerange()使这一点更加容易。

Example:

例子:

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

#3

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?

这很好，对吧?我可以用扩展方法或者助手来抽象它，但是在某个地方我们仍然要做一个foreach，对吧?

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();

#4

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

#5

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

我知道已经很晚了，但如果有人需要一个简单的解决方案，最酷的是你也可以加上where子句:

        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.

注意:只测试了MSSQL2008。

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

更新:当EF生成带有参数的sql语句时，上面的解决方案将不起作用，所以下面是对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.

它需要一点反思，但效果很好。

#6

For anyone using EF5, following extension library can be used: https://github.com/loresoft/EntityFramework.Extended

对于任何使用EF5的人，可以使用以下扩展库:https://github.com/loresoft/EntityFramework.Extended

context.Widgets.Delete(w => w.WidgetId == widgetId);

#7

EF 6.1

EF 6.1

public void DeleteWhere<TEntity>(Expression<Func<TEntity, bool>> predicate = null) 
where TEntity : class
{
    var dbSet = context.Set<TEntity>();
    if (predicate != null)
        dbSet.RemoveRange(dbSet.Where(predicate));
    else
        dbSet.RemoveRange(dbSet);

    context.SaveChanges();
} 

Usage:

用法:

// Delete where condition is met.
DeleteWhere<MyEntity>(d => d.Name == "Something");

Or:

// delete all from entity
DeleteWhere<MyEntity>();

#8

Still seems crazy to have to pull anything back from the server just to delete it, but at least getting back just the IDs is a lot leaner than pulling down the full entities:

仍然看起来很疯狂，需要从服务器上删除任何东西，只是为了删除它，但是至少返回id比删除完整的实体要精简得多:

var ids = from w in context.Widgets where w.WidgetId == widgetId select w.Id;
context.Widgets.RemoveRange(from id in ids.AsEnumerable() select new Widget { Id = id });

#9

For EF 4.1,

EF 4.1,

var objectContext = (myEntities as IObjectContextAdapter).ObjectContext;
objectContext.ExecuteStoreCommand("delete from [myTable];");

#10

The quickest way to delete is using a stored procedure. I prefer stored procedures in a database project over dynamic SQL because renames will be handled correctly and have compiler errors. Dynamic SQL could refer to tables that have been deleted/renamed causing run time errors.

删除的最快方法是使用存储过程。比起动态SQL，我更喜欢在数据库项目中存储过程，因为renames将被正确处理，并且有编译错误。动态SQL可以引用已删除/重命名导致运行时错误的表。

In this example, I have two tables List and ListItems. I need a fast way to delete all the ListItems of a given list.

在本例中，我有两个表列表和ListItems。我需要一种快速的方法来删除给定列表中的所有listitem。

CREATE TABLE [act].[Lists]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(50) NOT NULL
)
GO
CREATE UNIQUE INDEX [IU_Name] ON [act].[Lists] ([Name])
GO
CREATE TABLE [act].[ListItems]
(
    [Id] INT NOT NULL IDENTITY, 
    [ListId] INT NOT NULL, 
    [Item] NVARCHAR(100) NOT NULL, 
    CONSTRAINT PK_ListItems_Id PRIMARY KEY NONCLUSTERED (Id),
    CONSTRAINT [FK_ListItems_Lists] FOREIGN KEY ([ListId]) REFERENCES [act].[Lists]([Id]) ON DELETE CASCADE
)
go
CREATE UNIQUE CLUSTERED INDEX IX_ListItems_Item 
ON [act].[ListItems] ([ListId], [Item]); 
GO

CREATE PROCEDURE [act].[DeleteAllItemsInList]
    @listId int
AS
    DELETE FROM act.ListItems where ListId = @listId
RETURN 0

Now the interesting part of deleting the items and updating Entity framework using an extension.

现在，使用扩展删除条目和更新实体框架的有趣部分。

public static class ListExtension
{
    public static void DeleteAllListItems(this List list, ActDbContext db)
    {
        if (list.Id > 0)
        {
            var listIdParameter = new SqlParameter("ListId", list.Id);
            db.Database.ExecuteSqlCommand("[act].[DeleteAllItemsInList] @ListId", listIdParameter);
        }
        foreach (var listItem in list.ListItems.ToList())
        {
            db.Entry(listItem).State = EntityState.Detached;
        }
    }
}

The main code can now use it is as

主代码现在可以使用as

[TestMethod]
public void DeleteAllItemsInListAfterSavingToDatabase()
{
    using (var db = new ActDbContext())
    {
        var listName = "TestList";
        // Clean up
        var listInDb = db.Lists.Where(r => r.Name == listName).FirstOrDefault();
        if (listInDb != null)
        {
            db.Lists.Remove(listInDb);
            db.SaveChanges();
        }

        // Test
        var list = new List() { Name = listName };
        list.ListItems.Add(new ListItem() { Item = "Item 1" });
        list.ListItems.Add(new ListItem() { Item = "Item 2" });
        db.Lists.Add(list);
        db.SaveChanges();
        listInDb = db.Lists.Find(list.Id);
        Assert.AreEqual(2, list.ListItems.Count);
        list.DeleteAllListItems(db);
        db.SaveChanges();
        listInDb = db.Lists.Find(list.Id);
        Assert.AreEqual(0, list.ListItems.Count);
    }
}

#11

If you want to delete all rows of a table, you can execute sql command

如果要删除表的所有行，可以执行sql命令。

using (var context = new DataDb())
{
     context.Database.ExecuteSqlCommand("TRUNCATE TABLE [TableName]");
}

TRUNCATE TABLE (Transact-SQL) Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

truncatetable (Transact-SQL)删除表中的所有行，而不记录单独的行删除。truncatetable与DELETE语句相似，没有WHERE子句;但是，truncatetable更快，使用的系统和事务日志资源也更少。

#12

You can use extensions libraries for doing that like EntityFramework.Extended or Z.EntityFramework.Plus.EF6, there are available for EF 5, 6 or Core. These libraries have great performance when you have to delete or update and they use LINQ. Example for deleting (source plus):

您可以使用扩展库来实现这一点，比如EntityFramework。扩展或Z.EntityFramework.Plus。EF6，有EF 5, 6或Core。当您必须删除或更新这些库时，这些库具有很好的性能，它们使用LINQ。删除示例(源plus):

ctx.Users.Where(x => x.LastLoginDate < DateTime.Now.AddYears(-2)) .Delete();

ctx.Users。(x = > x。LastLoginDate < DateTime.Now.AddYears(2)delete();

or (source extended)

或(源扩展)

context.Users.Where(u => u.FirstName == "firstname") .Delete();

context.Users。(u = > u。FirstName = =“FirstName”)delete();

These use native SQL statements, so performance is great.

它们使用本地SQL语句，因此性能非常好。

#13

You can also use the DeleteAllOnSubmit() method by passing it your results in a generic list rather than in var. This way your foreach reduces to one line of code:

您还可以使用DeleteAllOnSubmit()方法，将结果传递到泛型列表而不是var中。

List<Widgets> widgetList = context.Widgets
              .Where(w => w.WidgetId == widgetId).ToList<Widgets>();

context.Widgets.DeleteAllOnSubmit(widgetList);

context.SubmitChanges();

It probably still uses a loop internally though.

它可能仍然在内部使用一个循环。

#14

EF 6.=>

EF 6。= >

var assignmentAddedContent = dbHazirBot.tbl_AssignmentAddedContent.Where(a =>
a.HazirBot_CategoryAssignmentID == categoryAssignment.HazirBot_CategoryAssignmentID);
dbHazirBot.tbl_AssignmentAddedContent.RemoveRange(assignmentAddedContent);
dbHazirBot.SaveChanges();

#15

UUHHIVS's is a very elegant and fast way for batch delete, but it must be used with care:

UUHHIVS是一种非常优雅、快速的批量删除方式，但必须小心使用:

• auto generation of transaction: its queries will be encompassed by a transaction
• 自动生成事务:其查询将被事务包含
• database context independence: its execution has nothing to do with context.SaveChanges()
• 数据库上下文独立性:它的执行与context. savechanges()无关。

These issues can be circumvented by taking control of the transaction. The following code illustrates how to batch delete and bulk insert in a transactional manner:

这些问题可以通过控制交易来规避。下面的代码演示了如何以事务性方式批量删除和批量插入:

var repo = DataAccess.EntityRepository;
var existingData = repo.All.Where(x => x.ParentId == parentId);  

TransactionScope scope = null;
try
{
    // this starts the outer transaction 
    using (scope = new TransactionScope(TransactionScopeOption.Required))
    {
        // this starts and commits an inner transaction
        existingData.Delete();

        // var toInsert = ... 

        // this relies on EntityFramework.BulkInsert library
        repo.BulkInsert(toInsert);

        // any other context changes can be performed

        // this starts and commit an inner transaction
        DataAccess.SaveChanges();

        // this commit the outer transaction
        scope.Complete();
    }
}
catch (Exception exc)
{
    // this also rollbacks any pending transactions
    scope?.Dispose();
}

#16

See the answer 'favorite bit of code' that works

参见“最喜欢的代码”的答案

Here is how I used it:

我是这样使用的:

     // Delete all rows from the WebLog table via the EF database context object
    // using a where clause that returns an IEnumerable typed list WebLog class 
    public IEnumerable<WebLog> DeleteAllWebLogEntries()
    {
        IEnumerable<WebLog> myEntities = context.WebLog.Where(e => e.WebLog_ID > 0);
        context.WebLog.RemoveRange(myEntities);
        context.SaveChanges();

        return myEntities;
    }

#17

You can execute sql queries directly as follows :

您可以直接执行sql查询如下:

    private int DeleteData()
{
    using (var ctx = new MyEntities(this.ConnectionString))
    {
        if (ctx != null)
        {

            //Delete command
            return ctx.ExecuteStoreCommand("DELETE FROM ALARM WHERE AlarmID > 100");

        }
    }
    return 0;
}

For select we may use

选择我们可以使用。

using (var context = new MyContext()) 
{ 
    var blogs = context.MyTable.SqlQuery("SELECT * FROM dbo.MyTable").ToList(); 
}

#18

Best : in EF6 => .RemoveRange()

最佳:在EF6 => .RemoveRange()中

Example:

例子:

db.Table.RemoveRange(db.Table.Where(x => Field == "Something"));