Entity Framework RemoveRange is very slow-CodePudding

I am deleting hundreds of rows from my table. Using ADO.Net code i.e. Delete from table where somecolumn = somestring takes less than a second while using Entity Framwork i.e.

MyDbContext context = new MyDbContext()    
context.SomeEntity.RemoveRange(context.SomeEntity.Where(i => i.somecolumn == somestring));
context.SaveChanges();

is taking 8-10 seconds.

can anybody explain it or I am doing something wrong.

CodePudding user response：

In these situations which you want to delete among of records (read thousand or more) , the most efficient way would be a so called "bulk delete". EFCore.BulkExtensions allows that. Some thing like below code:

var recordsToRemove = context.SomeEcontity.Where(i => i.somecolumn == somestring));

context.BulkDelete(recordsToRemove);

CodePudding user response：

EF is designed around providing relational data mapped to an object model. It isn't ideally suited to large bulk operations. Still, you can facilitate one-off requirements like this a number of ways.

The first method would be to use stubs for the delete. For this you would want to ensure that the DbContext instance is "clean" of any tracked instances that might be deleted, so ideally a DbContext that is scoped to that method.

using (var context = new SomeDbContext())
{
    var stubs = context.SomeEntities
        .Where(x => x.SomeColumn == someString)
        .Select(x => x.Id)
        .ToList()
        .Select(x => new SomeEntity { Id == x })
        .ToList();
}

Now you might be able to simplify that down to:

var stubs = context.SomeEntities
    .Where(x => x.SomeColumn == someString)
    .Select(x => new SomeEntity { Id == x.Id })
    .ToList();

However, you would probably want to test that to ensure that the resulting SQL is just selecting the ID (not the entire entity) and that context.SomeEntities.Local.Any() is still false... The first example will just ensure that the query load the IDs, then proceeds to build stub Entities using that ID. This makes the "selection" for our data as efficient as possible.

From here you should be able to use RemoveRange on the untracked stubs.

context.SomEntities.RemoveRamge(stubs);
context.SaveChanges();

The important detail is that the DbContext cannot be tracking any of these entities as this will temporarily attach these stubs to the DbContext. If the context was already tracking an instance with one of those ids then you would receive an error that one or more entities with the same ID was already being tracked. (Hence the locally scoped DbContext to avoid that)

The other way to perform this deletion would be to issue a direct SQL operation. If you have a DbContext that is scoped to a request or longer than this single operation then this should be done after dealing with any currently tracked instances.

Step 1. Deal with any tracked instances if you have an injected DbContext:

var trackedInstances = context.SomeEntities.Local
     .Where(x => x.SomeColumn == someString);
     .ToList();

if (trackedInstances.Any())
    context.SomeInstances.RemoveRange(trackedInstances);

This will check the DbContext for any tracked instances without hitting the DB. We will want to remove these instances to avoid possibly having any of these marked as Modified and triggering an exception later during a SaveChanges call.

Step 2. Build and run a parameterized raw SQL statement to clean off all remaining rows in the DB.

context.Database.ExecuteSqlCommand(@"DELETE FROM dbo.SomeEntities
        WHERE SomeColumn = @someString", new SqlParameter("someString", someString));
context.SaveChanges();

The important detail here is to use a parameterized query. Do not execute raw SQL with the parameters embedded in the string as this leaves the door open to SQL injection attacks.

I.e. do not use anything like:

context.Database.ExecuteSqlCommand($"DELETE FROM dbo.SomeEntities 
        WHERE SomeColumn = '{someString}'");
// or
context.Database.ExecuteSqlCommand("DELETE FROM dbo.SomeEntities 
        WHERE SomeColumn = '"   someString   "'");