Entity Framework. Delete All Rows in Table

Delete all rows from a table with an Entity Framework function using C#

Raw SQL is the preferred method to perform bulk changes like this. But the best way to do it otherwise is to fetch all the IDs and construct "Stub Entity" instances with only the IDs populated.

EG

var toDelete = db.Alerts.Select(a => new Alert { Id = a.Id }).ToList();
db.Alerts.RemoveRange(toDelete);
db.SaveChanges();

How can I efficiently delete all records in a table using Entity Framework without using SQL?

This is currently not something that is possible using Entity Framework. see https://github.com/dotnet/efcore/issues/795

There may be an extension out there that will allow you to do that, but I am not sure it will work will all RDBMS systems.

How do I delete multiple rows in Entity Framework (without foreach)

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.

Entity Framework: How do I delete table rows that reference each other?

Did you verify if UserLicenses and SerialsOnOrderDetails collections are properly loaded and not empty ? Are you sure removerange is the proper way to do it ? I suggest you to read a few tutorials about EF if you are not used to it.

Maybe you'll have to update GetOrderById with .Include("....") directives to load these collections, or load the related items manually.

How do I delete multiple rows in Entity Framework Core?

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!

How do I delete a set of records in Entity Framework .Core?

Since you have already gotten the list of associated notes, then use RemoveRange on the DbSet, removing each element. Save changes can be applied after.

//...

var HCF = await _context.HCF.FindAsync(id);
_context.HCF.Remove(HCF);

IQueryable<HCReportingNote> notesQuery =
    from n in _context.HCReportingNotes 
    where n.HCFId == HCF.ID
    select n;

 _context.HCReportingNotes.RemoveRange(notesQuery);

await _context.SaveChangesAsync();

//...

Removing thousands of rows from table using EF6

Can you try like this :

using (someEntites dc = new someEntites())
{
    //var listForRemoval = (from a in dc.someTable
    //where a.Year == 2018 && a.month == 04
    //select a).ToList();

    //if (listForRemoval?.Count > 0)
    //{
    //dc.someTable.RemoveRange(listForRemoval);
    //dc.SaveChanges();
    //}

    dc.Database.ExecuteSqlCommand("DELETE FROM someTable " + 
                                  "WHERE Year = {0} " + 
                                  "AND month = {1}", 2018, 4); 
    // Executes a commande with parameters. You can add more parameters separated by ','.
}

If there are relations with other tables, you should delete them separately.

Delete all related rows to user using EF .NET CORE

You rely on CascadeOnDelete when deleting your Users. This is a relatively slow process, because for every user that is to be deleted, your database management system will have to check all related tables to see if there is a relation with the user that is to be deleted.

It is way faster to first delete the related objects and then delete the customer:

using(var dbContext =  new MyDbContext(...))
{
    Customer userToDelete = dbContext.Users.Where(...).FirstOrDefault();

    // before deleting this user, remove all related items
    var rolesToRemove = dbContext.Roles.Where(role => role.UserId == userToDelete.Id);
    dbContext.Roles.RemoveRange(rolesToRemove.ToList());

    var notificationsToRemove = dbContext.Notifications
        .Where(notification => notification.UserId == userToDelete.Id);
    dbContext.Notifications.RemoveRange(notificationsToRemove.ToList());

    ... // etc.
    dbContext.User.Remove(userToDelete();
    dbContext.SaveChanges();
}

The problem with Entity Framework is, that you need to fetch the items before you can remove them.

If you need to remove users often, then you can bypass this fetching by creating a stored procedure.

class MyDbContext : DbContext
{
    public DbSet<User> Users {get; set;}
    ... // etc

    protected override OnModelCreating(...)
    {
         ... // fluent API, table names, column names, relations between tables, ...

         this.CreateStoredProcedureRemoveUser()
    }

    private void CreateStoredProcedureUpdateUsageCosts(DemoContext context)
    {
        const string sqlText = @"Create Procedure RemoveUser @UserId int as
        Begin
           ... // Sql code to remove UserMeetings, Roles, Groups, etc
           ... // Sql code to remove the user
        End";

        this.Database.ExecuteSqlComment(sqlText);
    }

    // Procedure to remove the user:
    public void RemoveUser(int userId)
    {
        const string sqlCommandRemoveUser= @"Exec RemoveUser @UserId";
        object[] commandParameters = new object[]
        {
            new SqlParameter(@"@UserId", userId),
        };
        this.Database.ExecuteSqlCommand(sqlCommandRemoveUser, commandParameters);

    }
}

Usage:

int userId = ...
using (var dbContext = new MyDbContext())
{
    dbContext.RemoveUser(userId);
}

Careful: because the procedure is executed immediately you can't go back once you've called the method. If you want a fallback, for instance, you get an exception when removing Groups after the Roles are removed, use DbContext.Database.BeginTranscation / EndTransaction, to get back to the original situation.

---

Related Topics