From reading many other posts it looks like I need to use
context.DbSet<Table>.RemoveRange(…);
context.SaveChanges();
to efficiently remove multiple entities.
Sadly however, in my scenario, this is still taking far too long. In tests, even removing 5 entities with about 10 fields takes about 1 sec per entity. This is far too slow.
What else can I do to improve performance?
This is what the method looks like that does the work:
public void RemoveClassReportGroupings(IEnumerable<(int clientClassId, int classReportGroupingId)> enumerable)
{
List<Class_ReportGrouping> removeItems = new List<Class_ReportGrouping>();
var dict = _context.ClassReportGroupings.Select(i => i).ToDictionary(i=> (i.ClassId, i.GroupingId));
foreach (var item in enumerable)
{
var removeItem = dict[(item.clientClassId ,item.classReportGroupingId)];
removeItems.Add(removeItem);
}
_context.ClassReportGroupings.RemoveRange(removeItems);
}
I guess, that this has nothing to do with EF, but with database optimization.
SaveChanges
is the place where Entity Framework actually compiles your LINQ query into a suitable SQL query and executes it to the Database.
You may want to retrieve the query EF created for deletion for manual examination and or have a look at the possible query optimizations using a tool like MS SQL SQL Server Management Studio (SSMS)
There are plenty of resources about query analysis and optimizations for SQL server, have a look around these, e.g from Microsoft (retired, but still relevant)