46

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!

Improve this question
0

5 Answers 5

Reset to default
56

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I'm not sure the Clear option works correctly in EF Core 2.0. I just got stung by this. Iterating through and removing entries worked fine though.
@Kinetic did you find out the actual behaviour in the end?
It's been a while, no I can't remember where I got to with this. It's probably just yet another limitation with ef core for the moment. If my datasets are small i probably just stuck to the looped method, otherwise a spot of sql / stored proc.
@Kinetic - when you did do this, do you remember if it was on a parent entity whose children were .Include()'d? I haven't done it, but, If you do a .Clear() on an untracked list of children, it probably doesn't do anything.
14

As of EF Core 7.0, you can now do Bulk Deletes. So, this should get the job done:

await _context.MyEntities.ExecuteDeleteAsync();

And, you can include a filter if needed:

await _context.MyEntities
    .Where(x => x.CreatedDate < DateTime.Now.AddYears(-10))
    .ExecuteDeleteAsync();
Improve this answer

Comments

12

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.

Improve this answer

5 Comments

Probably worth noting that EFCore.BulkExtensions doesn't support MySQL. github.com/borisdj/EFCore.BulkExtensions/issues/71
Why use an external dependency when bulk operations are supported by core
@johnny5 Could you please be clearer that how you can do a bulk delete without an external dependency in ef core?
Just to show how you can delete by query context.Items.Where(a => a.ItemId > 500).BatchDeleteAsync();
Clearly, fetching the records first and passing a list for deletion is quite suboptimal compared to passing the filtering condition directly into the DELETE statement. It's a shame this approach is considered as fast.
6

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

Improve this answer

Comments

-1

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");
}
Improve this answer

2 Comments

This example doesn't really answer the question, but also there's no need to call SaveChanges() multiples times; you can just call it once at the end of the method to reduce the roundtrips to the db. Also the second _db.SaveChangesAsync() is missing an await. Finally, if there's often many AssetImages you're probably better off configuring EFcore to cascade the Asset delete to the AssetImage, or doing a raw sql delete so you only need one db call instead of one per AssetImage.
@rory, “ you can just call it once at the end of the method to reduce the roundtrips to the db”. It will be one transaction and if it will failed, all removes will be rollback. It may or may not be what you want.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.