This is actually something that EF is not made for. EF's database interactions start from the record object, and flow from there. EF cannot generate a partial UPDATE (i.e. not overwriting everything) if the entity wasn't change tracked (and therefore loaded), and similarly it cannot DELETE records based on a condition instead of a key.
There is no EF equivalent (without loading all of those records) for conditional update/delete logic such as
UPDATE People
SET FirstName = 'Bob'
WHERE FirstName = 'Robert'
or
DELETE FROM People
WHERE FirstName = 'Robert'
Doing this using the EF approach will require you to load all of these entities just to send them back (with an update or delete) to the database, and that's a waste of bandwidth and performance as you've already found.
The best solution I've found here is to bypass EF's LINQ-friendly methods and instead execute the raw SQL yourself. This can still be done using an EF context.
using (var ctx = new MyContext())
{
string updateCommand = "UPDATE People SET FirstName = 'Bob' WHERE FirstName = 'Robert'";
int noOfRowsUpdated = ctx.Database.ExecuteSqlCommand(updateCommand);
string deleteCommand = "DELETE FROM People WHERE FirstName = 'Robert'";
int noOfRowsDeleted = ctx.Database.ExecuteSqlCommand(deleteCommand);
}
More information here. Of course don't forget to protect against SQL injection where relevant.
The specific syntax to run raw SQL may vary per version of EF/EF Core but as far as I'm aware all versions allow you to execute raw SQL.
I can't comment on the performance of EF Extensions or BulkUpdate specifically, and I'm not going to buy it from them.
Based on their documentation, they don't seem to have the methods with the right signatures to allow for conditional update/delete logic.
BulkUpdate doesn't seem to allow you to input the logical condition (the WHERE in your UPDATE command) that would allow you to optimize this.BulkDelete still has a BatchSize setting, which suggests that they are still handling the records one at a time (well, per batch I guess), and not using a single DELETE query with a condition (WHERE clause).
Based on your intended code in the question, EF Extensions isn't really giving you what you need. It's more performant and cheaper to simply execute raw SQL on the database, as this bypasses EF's need to load its entities.
Update
I might stand corrected, there is some support for conditional update logic, as seen here. However, it is unclear to me while the example still loads everything in memory and what the purpose of that conditional WHERE logic then is if you've already loaded it all in memory (why not use in-memory LINQ then?)
However, even if this works without loading the entities, it's still:
- more limited (only equality checks are allowed, compared to SQL allowing any boolean condition that is valid SQL),
- relatively complex (I don't like their syntax, maybe that's subjective)
- and more costly (still a paid library)
compared to rolling your own raw SQL query. I would still suggest rolling your own raw SQL here, but that's just my opinion.
ToList()" on your query, you materialize it, meaning load everything into memory. What are you trying to update more precisely? Is this helping: #44195377 ? – Enforce