How do I delete multiple rows in Entity Framework Core?

Question

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!

Answer 1

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!

Answer 2

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.

Answer 3

I have created a library to batch delete or update records with a round trip on EF Core 5.

Sample code as follows:

await ctx.DeleteRangeAsync(b => b.Price > n || b.AuthorName == "zack yang");

await ctx.BatchUpdate()
.Set(b => b.Price, b => b.Price + 3)
.Set(b=>b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper())
.Set(b => b.PubTime, b => DateTime.Now)
.Where(b => b.Id > n || b.AuthorName.StartsWith("Zack"))
.ExecuteAsync();

Github repository

Report of the library

Answer 4

You can do something like this

[HttpDelete("{id}")]
public async Task<ActionResult<string>> DeleteAsset(int id)
{
    _db.Assets.Remove(_db.Assets.Find(id));
    await _db.SaveChangesAsync();
    // Đi tìm trang table asset_image
    List<AssetImage> assetImageList = _db.AssetImages.Where(x => x.AssetId == id).ToList();
    foreach(AssetImage assetImageItem in assetImageList)
    {
        _db.AssetImages.Remove(assetImageItem);
        _db.SaveChangesAsync();
    }
    return Ok("ok");
}