MySQL Relational Database Server and Its Tools
View All Tags
A client gives up and closes the connection while your transaction is halfway through. Does the work stop? Is half of it saved? I tried it both ways. The transaction is safe either way, and one case doesn't behave the way the CancellationToken suggests.
A row lock lasts until the transaction ends, not until the statement ends. So a payment call, an email or a file upload inside a transaction keeps that row locked the whole time, and everyone who wants it waits. I measured it: moving one slow call out took another request from 2.5 seconds to 0.013.
A connection that drops just after MySQL commits and just before your app hears about it is rare, which means your retry code probably hasn't met it. You can make it happen on demand with a small proxy, then see for yourself whether a retry repeats the work. Everything here is for your own machine.
A client that gets no answer can't tell whether its POST worked. If it tries again, you can get two orders. An Idempotency-Key header fixes that: the client sends the same key each time, and the second request gets back what the first one made.
SumAsync adds up a column in the database, without loading the rows. Two things are worth seeing: what it returns when there are no rows, since SQL's SUM gives NULL there, and how to get a total per group.
To load several rows by their IDs, call Contains on the list inside your Where. It reads backwards the first time, and EF Core turns it into a plain SQL IN. An empty list is worth seeing too: it doesn't fail, and it doesn't return everything.
Most apps never need this, because one DbContext is enough. When you do have two, such as separate contexts for separate modules, they can share one transaction: they need the same connection, and the second has to be told about the transaction. Here it is, working and rolling back.
TransactionScope wraps code in a transaction without passing anything around: everything on the connection inside it joins in, and it commits only if you call Complete(). It works with EF Core and MySql.Data, with three things to get right. If you don't need its ambient behaviour, BeginTransactionAsync is simpler.
Could not save changes. Please configure your entity type accordingly. tells you nothing, and it isn't meant to be read on its own. The real error is one level down, with a number you can test in code. Where it sits depends on which method threw, so a small helper is worth having.
LINQ covers most queries, and now and then you need SQL of your own: FOR UPDATE, SELECT VERSION(), or a query LINQ makes awkward. EF Core has three methods for that, and which one you use depends on what you want back.
