Databases, Data Modeling, and Data Access
View All Tags
EF Core has three common ways to read one row: FirstOrDefaultAsync, SingleOrDefaultAsync and FindAsync. They look the same on a happy day and differ in what they send to MySQL and when they throw. This guide runs all three on the same row and compares the SQL, so you can pick with evidence.
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.
AddRange adds a list of new objects to the change tracker, and one SaveChangesAsync inserts them all. With Oracle's MySQL provider, EF Core packs the INSERT statements into one command to save round trips, and every object gets its own ID. This guide shows the code and the SQL it sends for 3 rows and for 50.
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.
