All the ways to Update/Delete entity with EF
With ORM it is always a tradeoff. You no longer have to deal with SQL, in exchange for performance. Despite that, EF team continue improving autogenerated SQL queries, there were always operation that perform badly no matter how we try, so the end up solution would always be to write SQL itself.
An example of those operations would be Update and Delete. Let’s see what is exactly the issue with those, and how can it be fixed.
Update
Single entity update
To update single entity, you would probably go with something like this:
There is nothing supernatural here (for now 😈). We need to load our entity into memory, so the tracking mechanism of dbContext get aware of it, then we do an update and save everything.
The code above will generate next SQL:
Notice we're selecting Age field even if it is not needed.
So far, so good. Let’s move to the next example, where stuff gets a little more complicated.
Single entity without Select request
What if we really want to be efficient here and avoid that redundant SELECT query. It is still possible:
Just create an entity with given ID and field you want to modify, attach that entity, so the tracking mechanism of dbContext get aware of it, mention which field is modified, and we are done.
Generated SQL:
If you provide wrong ID, EF will throw an exception. Another drawback of this approach, that if you forget to mention which field is modified and accidentally use Update(), EF will set other fields to their default value. So be careful here.
Alright, let’s move on.
Multiple entities update
Imagine we need to update multiple entities. We would need to do it the same way we did before. Load them all into memory, change the field and save.
And that is exactly where we got to a performance issue. Do you know how many entities are loaded in the example above? It may be one, it may be thousands.
This will result in next SQL:
Not only all those entities are loaded into memory, we also would have as many UPDATE queries.
Jesus Christ, nobody told me that 😰Maybe we can improve it with those fancy
AsyncEnumerable?
Nope. I mean, you got a little performance boost, but it is still the same SQL.
Alright, I saw that
ForEach()method. Does it work?
I am sorry to tell you. Even though it looks like we are not fetching entities here, it still will do it in the background, so we end up with the same SQL.
Alright, alright I gave up. Is there anything we can do?
There weren’t. Until EF 7 came up. The new method is called ExecuteUpdate() and can be used for both single and multiple entities scenarios:
Finally, we get what we want, single SQL statement 😌:
However, have you noticed lack of SaveChangesAsync() method? The tracking mechanism of dbContext does not know anything about those entities. Nothing is loaded into memory. Obviously that is a huge improvement, but also with a huge cost. Be careful, so you don't end up working with outdated entities:
Delete
With Delete operation, the situation is pretty much the same.
Want to delete something, first load it into memory:
Do you know the ID? Alright, we can avoid loading it:
However, deleting a butch:
Will result in SQL hell:
Until EF 7:
And this is something that our SQL Server can handle without problems:
It is not like it was not possible before:
At least now, we don't get to deal with SQL and have benefits of strongly typed name 😀
Summary
Here, we briefly discussed different ways to update and delete entities from database. As you can see, some simple solution can lead to huge performance issues for SQL server. On the other hand, even though bulk operation works faster, they can lead to unexpected behavior. Only you're the one to chose which suits better.
Thanks for reading.
Clap whenever you found something new 👏
Buy me a coffee using link below ☕️
And Follow to not miss more articles about EF ✅
