176 posts tagged with "Database"

SaveChangesAsync loads rows, changes them in memory and writes them back. When you already know the change, that's wasted effort. ExecuteUpdateAsync sends one UPDATE straight to MySQL, for one row or thousands. This guide shows it in use, reads the SQL, and demonstrates the two ways it can surprise you.

To delete a row with EF Core, load it, call Remove, and call SaveChangesAsync. A Web API also needs to answer the two things that can go wrong: the row isn't there, and other rows still depend on it. This guide does all of that, with the real SQL from MySQL.

To update a row with EF Core, load it, change the property, and call SaveChangesAsync. There's no Update call in that, and EF Core sends an UPDATE with only the columns that changed. If nothing changed, it sends nothing at all. This guide shows that in the real SQL, and compares it with the Update method.

EF Core decides what SQL to send by looking at each entity's state. There are five states, and once you know them, SaveChangesAsync stops being mysterious. This guide follows one entity through all of them, printing the state at each step and showing the SQL each save sends.

AnyAsync answers one question: is there at least one row like this? It sends SELECT EXISTS and returns true or false, so nothing gets loaded or counted. This guide shows the call, the SQL, and the one thing it can't do safely.

CountAsync asks MySQL how many rows match, without loading any of them. It's what you need for totals, such as "page 2 of 7". This guide shows its forms and the SQL each one sends.

A product belongs to a category, but a query for products doesn't load the category unless you ask. There are two ways to ask: Include and Select. They both produce a join and differ in how much they load. This guide runs each one on MySQL and reads the SQL, including what happens if you ask for neither.

By default, EF Core remembers every entity a query returns, in case you change it. AsNoTracking turns that off for one query, which saves work when you only want to read. This guide shows what it changes and what it doesn't, measures the difference on 5,000 rows, and explains the cases where you don't need it at all.

When several queries return the same response, you want one definition of how to build it. The obvious way, a normal C# method inside Select, loads every column, and throws NullReferenceException as soon as it touches a navigation. The same code written as an Expression lets EF Core read it, and it sends only the columns and joins it needs.

Reading a whole entity sends every column over the wire, even when you only use two. A Select call tells EF Core which values you want, and it asks MySQL for exactly those. This guide compares the SQL for both, maps the result to a record, and checks what happens to change tracking.