Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I delete multiple rows in Entity Framework Core?

I need to delete multiple rows from a database using Entity Framework Core.

This code does NOT work:

foreach (var item in items) {     myCollection.Remove(item); } 

because I get an error "InvalidOperationException: Collection was modified; enumeration operation may not execute" after the first object. In other words, .Remove removes only one object.

Entity Framework Core does NOT have .RemoveRange, so I have no idea how to perform this operation.

In order to preserve maximum compatibility with the various database providers, I would prefer NOT to call context.Database.ExecuteSqlCommand("delete from physical_table where..."). Is there a suitable solution? Thanks!

like image 306
Giorgio Borgo Avatar asked Jan 31 '17 14:01

Giorgio Borgo


People also ask

How do I delete a row in Entity Framework?

In Connected Scenario, you can use the Remove or RemoveRange method to mark the record as Deleted . In Disconnected Scenario, you can attach it to the context and set its state as Deleted . Calling SaveChanges will send the delete query to the database.

How do I delete my EF core database?

Deleting an entity is done using the Remove or RemoveRange method of the DbSet. Alternatively, you can also set the entity state as Deleted . We can delete records either in connected or disconnected Scenarios. We will also look at how to remove multiple records from the database using the RemoveRange method.

How delete multiple records from database in MVC?

Choose your data connection then select your database then click Next then select Tables then enter the Model namespace then click Finish. Add a Controller or use the default Home controller. I used the default Home controller. Add a new action into your controller to delete rows.


2 Answers

because I get an error "InvalidOperationException: Collection was modified; enumeration operation may not execute" after the first object. In other words, .Remove removes only one object.

This has nothing to do with EF Core, and, yes, .Remove() only removes one object. However, you are attempting to modify a collection that you are iterating through. There are ways to do this, but this isn't a good route to go.

Entity Framework Core does NOT have .RemoveRange, so I have no idea how to perform this operation.

There are definitely at least a couple simple ways to delete multiple records in EF Core. And, EF Core does have a RemoveRange() method - it's a method on DbSet<TEntity>, see here in the API docs (as stated in the comment above).

A couple options:

  1. If myCollection is of a type that belongs to a DbSet<TEntity>, a simple call such as this will do the trick:

    _dbContext.MyEntities.RemoveRange(myCollection); _dbContext.SaveChanges(); 
  2. If myCollection is actually a navigation property off of an entity that you queried, you can call .Clear() on the collection instead of iterating and calling .Remove().

    var myParentEntity = _dbContext.MyParentEntities                          .Include(x => x.MyChildrenEntities)                          .Single(x => x.Id == id); myParentEntity.MyChildrenEntities.Clear(); _dbContext.SaveChanges(); 

As also commented above, there's a lot of context missing on your question - more complete code should be posted. I'm just taking a couple stabs in the dark to get you up and running with EF Core!

like image 122
steamrolla Avatar answered Sep 21 '22 12:09

steamrolla


If you want to remove many items (read hundreds or more) on some arbitrary filter, the most efficient way would be a so called "bulk delete". EFCore.BulkExtensions allows that. Check an example below:

var toRemoveModels = DataAccess.ModelRepository.All     .Where(m => m.Name.StartsWith("Added model"))     .ToList(); DataAccess.ModelRepository.BulkDelete(toRemoveModels); 

where the actual implementation within the database context is as simple as:

public void BulkDelete<TModel>(IList<TModel> entities) where TModel: class {     this.BulkDelete(entities, bulkConfig: null); } 

This will generate a bunch of queries, but will still be more efficient than issuing lots of DELETE statements:

SELECT [m].[Id], [m].[MakeId], [m].[Name], [m].[PriceInEur] FROM [Model] AS [m] WHERE [m].[Name] LIKE N'Added model' + N'%' AND (LEFT([m].[Name], LEN(N'Added model')) = N'Added model') go SELECT columnproperty(object_id('dbo.[Model]'),'Id','IsIdentity'); go SELECT TOP 0 T.[Id] INTO dbo.[ModelTemp208f3efb] FROM dbo.[Model] AS T LEFT JOIN dbo.[Model] AS Source ON 1 = 0; go select @@trancount; SET FMTONLY ON select * from dbo.[ModelTemp208f3efb] SET FMTONLY OFF exec ..sp_tablecollations_100 N'[dbo].[ModelTemp208f3efb]' go insert bulk dbo.[ModelTemp208f3efb] ([Id] Int) go MERGE dbo.[Model] WITH (HOLDLOCK) AS T USING dbo.[ModelTemp208f3efb] AS S ON T.[Id] = S.[Id] WHEN MATCHED THEN DELETE; go DROP TABLE dbo.[ModelTemp208f3efb] go 

Note: a more efficient way of performing a "bulk" delete would be by providing an IQueryable which specifies the way items should be fetched and generates a DELETE similar to the following one:

DELETE FROM SomeTable WHERE Id IN (SELECT Id FROM SomeTable WHERE ...) 

This is faster because it does not require to load EF entities, nor create temporary table and MERGE against it.

I have used a library for Entity Framework 6, but could not find a non-commercial one for EF Core.

like image 45
Alexei - check Codidact Avatar answered Sep 21 '22 12:09

Alexei - check Codidact