Bulk operations in EntityFramewok Core

Reason for this article

I got into a project, in which, one of the solutions for the business needs was to delete and update some lists of entities after they got processed (with complicated logic) in some jobs. The technical solution for this was to use, EFCore.BulkExtensions, without doing good research beforehand Because of multiple issues, I started to do some research for alternatives, and this article is the result of it.

Technical solutions for the business needs

Option 1

As already mentioned, EFCore.BulkExtensions might have been one of the approaches, but in this case, wasn’t the best one. The idea behind it is that it creates some temp tables, inserts the data in there, merges it, and cleans the tables out. The problem appears, when you have one single instance of the database, with multiple services hitting it, or your database server simply gets low on hardware (aka CPU usage over 98%, memory over 80% things like that). At that moment everything goes crazy, some merges might not be done, and your data will be everywhere, some tables might not be dropped after everything is done, and so on.
You might now say that if you are poor, you should change the database type; well, it might be an idea, but in this case, you have other options on the market that helps you not to have these problems, so, why not use them? In the end, it’s not the database's fault for what’s happening.
Why this package is used? Well, it was pretty popular some years back, it got into the Top 20 extensions recommended by Microsoft, so this is why it is still used. To be honest, even makes sense to use it since it’s pretty fast and easy to use, but to really make sense, you need to have at least a list of 10000 elements. I won’t give examples of how to use this extension since you have tons of them on their GitHub page.

Option 2

The worst method is to do a loop over all the elements from the lists and delete/update each one, one by one. This is the worst method so, I wouldn’t recommend it in any way. I don’t even think it’s necessary to give more explanations why is it so bad, but it’s still an option!

Options 3

You can make some magic with .NET 7

dbContext.Items.Where(s=> myObjList.Select(d=>d.Id).ToList().Contains(s.Id))
           .ExecuteDelete();

The code looks ugly, but the idea behind it is to use LINQ to get the statement you want and use ExecuteDelete to delete all the objects. At the moment of this execution, only one call will be made to the database. More examples can be found here.

Option 4

db.Items.RemoveRange(db.Items.Where(x => x.State == "Active"));
db.SaveChanges();

This is another example of how you can remove multiple items. It doesn’t contain a list of IDs since I tried it only in a console app. The idea behind it is that it’s been there since Ef6, but the downfall of this is that on big datasets, it will kill your memory. Behind the scenes, Ef gets the data from the database and after that mark it as deleted and only then does it actually do the deletion, so you can imagine why the memory will get high.

Option 5

Last but not least, would be to write raw SQL and you can do this like this:

dbContext.Items.FromSqlRaw("delete...")

I don’t like to do this either but in theory, should be the fastest.

Conclusion

Stop doing CVBD (CV-based development), and think about what’s best for your project. be professional, and try out new things in pet projects.