How do I delete multiple rows in Entity Framework without foreach

Question

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   Right now I m doing this   var widgets   context Widgets      Where w   gt  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

User · Answer

Thanh s answer worked best for me  Deleted all my records in a single server trip  I struggled with actually calling the extension method  so thought I would share mine  EF 6    I added the extension method to a helper class in my MVC project and changed the name to  RemoveWhere   I inject a dbContext into my controllers  but you could also do a using      make a list of items to delete or just use conditionals against fields var idsToFilter   dbContext Products      Where p   gt  p IsExpired       Select p   gt  p ProductId       ToList        build the expression Expression lt Func lt Product  bool gt  gt  deleteList         a    gt  idsToFilter Contains a ProductId       Run the extension method  make sure you have  using namespace  at the top  dbContext RemoveWhere deleteList     This generated a single delete statement for the group

User · Answer

Entity Framework Core     3 1 3 0 2 2 2 1 2 0 1 1 1 0   using  YourContext context   new YourContext           var widgets   context Widgets Where w   gt  w WidgetId    widgetId       context Widgets RemoveRange widgets       context SaveChanges        Summary      Removes the given collection of entities from the context underlying the set       with each entity being put into the Deleted state such that it will be deleted       from the database when SaveChanges is called    Remarks      Note that if System Data Entity Infrastructure DbContextConfiguration AutoDetectChangesEnabled       is set to true  which is the default   then DetectChanges will be called once       before delete any entities and will not be called again  This means that in some       situations RemoveRange may perform significantly better than calling Remove multiple       times would do  Note that if any entity exists in the context in the Added state        then this method will cause it to be detached from the context  This is because       an Added entity is assumed not to exist in the database such that trying to delete       it does not make sense

User · Answer

Best   in EF6   gt   RemoveRange    Example   db Table RemoveRange db Table Where x   gt  Field     Something

User · Answer

You can execute sql queries directly as follows        private int DeleteData         using  var ctx   new MyEntities this ConnectionString                 if  ctx    null                           Delete command             return ctx ExecuteStoreCommand  DELETE FROM ALARM WHERE AlarmID  gt  100                         return 0      For select we may use  using  var context   new MyContext            var blogs   context MyTable SqlQuery  SELECT   FROM dbo MyTable   ToList

User · Answer

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 lt WebLog gt  DeleteAllWebLogEntries                 IEnumerable lt WebLog gt  myEntities   context WebLog Where e   gt  e WebLog ID  gt  0           context WebLog RemoveRange myEntities           context SaveChanges             return myEntities

User · Answer

EntityFramework 6 has made this a bit easier with  RemoveRange     Example   db People RemoveRange db People Where x   gt  x State     CA     db SaveChanges

User · Answer

I know it s quite late but in case someone needs a simple solution  the cool thing is you can also add the where clause with it   public static void DeleteWhere lt T gt  this DbContext db  Expression lt Func lt T  bool gt  gt  filter  where T   class       string selectSql   db Set lt T gt    Where filter  ToString        string fromWhere   selectSql Substring selectSql IndexOf  FROM         string deleteSql    DELETE  Extent1      fromWhere      db Database ExecuteSqlCommand deleteSql       Note  just tested with MSSQL2008   Update   The solution above won t work when EF generates sql statement with parameters  so here s the update for EF5   public static void DeleteWhere lt T gt  this DbContext db  Expression lt Func lt T  bool gt  gt  filter  where T   class       var query   db Set lt T gt    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   gt  field Name      internalQuery   Select field   gt  field GetValue query   First        var objectQuery   internalQuery GetType   GetFields BindingFlags NonPublic   BindingFlags Instance  Where field   gt  field Name      objectQuery   Select field   gt  field GetValue internalQuery   First   as ObjectQuery      var parameters   objectQuery Parameters Select p   gt  new SqlParameter p Name  p Value   ToArray         db Database ExecuteSqlCommand deleteSql  parameters       It requires a little bit of reflection but works well

User · Answer

EF 6 1  public void DeleteWhere lt TEntity gt  Expression lt Func lt TEntity  bool gt  gt  predicate   null   where TEntity   class       var dbSet   context Set lt TEntity gt         if  predicate    null          dbSet RemoveRange dbSet Where predicate        else         dbSet RemoveRange dbSet        context SaveChanges         Usage      Delete where condition is met  DeleteWhere lt MyEntity gt  d   gt  d Name     Something     Or      delete all from entity DeleteWhere lt MyEntity gt

User · Answer

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    Well  yes  except you can make it into a two-liner   context Widgets Where w   gt  w WidgetId    widgetId                  ToList   ForEach context Widgets DeleteObject   context SaveChanges

User · Answer

UUHHIVS s is a very elegant and fast way for batch delete  but it must be used with care    auto generation of transaction  its queries will be encompassed by a transaction database context independence  its execution has nothing to do with 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   gt  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

User · Answer

For EF 4 1    var objectContext    myEntities as IObjectContextAdapter  ObjectContext  objectContext ExecuteStoreCommand  delete from  myTable

User · Answer

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    ctx Users Where x   gt  x LastLoginDate  lt  DateTime Now AddYears -2             Delete     or  source extended   context Users Where u   gt  u FirstName     firstname        Delete     These use native SQL statements  so performance is great

User · Answer

context Widgets RemoveRange context Widgets Where w   gt  w WidgetId    widgetId  ToList     db SaveChanges

User · Answer

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   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

User · Answer

For anyone using EF5  following extension library can be used  https   github com loresoft EntityFramework Extended  context Widgets Delete w   gt  w WidgetId    widgetId

User · Answer

using  var context   new DatabaseEntities          context ExecuteStoreCommand  DELETE FROM YOURTABLE WHERE CustomerID    0    customerId

User · Answer

In EF 6 2 this works perfectly  sending the delete directly to the database without first loading the entities   context Widgets Where predicate  Delete      With a fixed predicate it s quite straightforward   context Widgets Where w   gt  w WidgetId    widgetId  Delete      And if you need a dynamic predicate have a look at LINQKit  Nuget package available   something like this works fine in my case   Expression lt Func lt Widget  bool gt  gt  predicate   PredicateBuilder New lt Widget gt  x   gt  x UserID    userID   if  somePropertyValue    null        predicate   predicate And w   gt  w SomeProperty    somePropertyValue     context Widgets Where predicate  Delete

User · Answer

If you don t want to execute SQL directly calling DeleteObject in a loop is the best you can do today    However you can execute SQL and still make it completely general purpose via an extension method  using the approach I describe here   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

User · Answer

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   In this example  I have two tables List and ListItems  I need a fast way to delete all the ListItems of a given list   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  gt  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   TestMethod  public void DeleteAllItemsInListAfterSavingToDatabase         using  var db   new ActDbContext                  var listName    TestList              Clean up         var listInDb   db Lists Where r   gt  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

User · Answer

If you want to delete all rows of a table  you can execute sql command  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

User · Answer

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   List lt Widgets gt  widgetList   context Widgets                Where w   gt  w WidgetId    widgetId  ToList lt Widgets gt      context Widgets DeleteAllOnSubmit widgetList    context SubmitChanges      It probably still uses a loop internally though

User · Answer

EF 6     var assignmentAddedContent   dbHazirBot tbl AssignmentAddedContent Where a   gt  a HazirBot CategoryAssignmentID    categoryAssignment HazirBot CategoryAssignmentID   dbHazirBot tbl AssignmentAddedContent RemoveRange assignmentAddedContent   dbHazirBot SaveChanges

[entity-framework] How do I delete multiple rows in Entity Framework (without foreach)

Examples related to entity-framework