Structured Query Language and Relational Queries
View All Tags
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.
With EF Core, you read rows by writing a LINQ query in C#, and EF Core turns it into SQL. The part that surprises people is timing: building the query sends nothing, and only ToListAsync or a similar call goes to MySQL. This guide reads rows with Where and OrderBy, shows the SQL each piece becomes, and proves the timing with real logs.
To insert a row with EF Core, add an object to its DbSet and call SaveChangesAsync. When that returns, the object's Id holds the number MySQL gave it, so you never have to query for it. This guide shows the two lines, the SQL they send, and how that new ID finds its way back.
In MySQL, 'usb' = 'USB' is true, and so is 'cafe' = 'café'. That comes from the collation, the rule that decides how text compares. Add COLLATE to a comparison to change the rule for that one query. This guide shows the default on MySQL 9.7, and three other rules you can ask for.
To find rows by part of a column's text in MySQL, use LIKE with a pattern, or LOCATE with plain text. They look alike and differ in what they do with %. This guide runs both on a real table on MySQL 9.7, including the part that surprises people: searching for usb finds USB.
After an INSERT, you usually need the new row's ID. After an UPDATE or a DELETE, you want to know whether it found anything. MySQL has a function for each: LAST_INSERT_ID() and ROW_COUNT(). They're simple, with a few behaviours worth seeing. All of it is run here on MySQL 9.7.
Your table's IDs go 1, 2, 5, 7, 12, and you can't account for the missing numbers. Nothing is wrong. MySQL hands out an AUTO_INCREMENT value before it knows whether the insert will succeed, and it doesn't take the value back. Here's that happening on MySQL 9.7, in two different ways.
A query can look fine and read a whole table to answer. EXPLAIN shows you how MySQL plans to run it, without running it. On MySQL 9.7 the default output is a tree, not the old table of columns many guides still show. This guide reads that tree on real queries, on a table with about 5,000 rows.
A product row holds a CategoryId, not a category's name. A JOIN brings the two tables together, so you can read both in one result. INNER JOIN keeps only the rows that match, and LEFT JOIN keeps every row from the first table. This guide runs both on MySQL 9.7, and shows a counting mistake that's easy to make.
SELECT COUNT(*) FROM table tells you how many rows a table has. Add WHERE to count only some of them, or GROUP BY to count per category. There's one trap: COUNT(column) skips NULLs, so it can give a smaller number than you expect. All of it is run here on MySQL 9.7.
