287 posts tagged with "MySQL"

FirstOrDefaultAsync() returned a row, and EF Core logged a warning with it: The query uses the 'First'/'FirstOrDefault' operator without 'OrderBy' and filter operators. This may lead to unpredictable results. The word "first" needs an order to mean anything, and SQL doesn't give you one by itself. An OrderBy or a Where makes the warning go away, and the answer reliable.

My paging endpoint answered 200 OK, and the log had a warning: The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results. It's right to warn. Without ORDER BY, MySQL returned the rows in the order of whichever index it happened to read, so the next query could give a different page. Adding a fully unique sort fixes it.

I added StringComparison.OrdinalIgnoreCase to a Contains in an EF Core query, to make sure the search ignored case. With Oracle's MySQL provider, it failed with an unusual message: Expression '[SqlExpressions.SqlFunctionExpression] COLLATE utf8mb4_bin' in the SQL tree does not have a type mapping assigned. The search was already ignoring case, because the column's collation does. Removing the argument fixed it.

A search for usb with Contains found five products named USB-C …. The same search with StartsWith found none, and neither threw an error. With Oracle's MySQL provider for EF Core, StartsWith and EndsWith compare with a case-sensitive collation, while Contains uses the column's own, which ignores case. EF.Functions.Like gives you a prefix search that follows the column.

I loaded a row to check it existed, built a new object with the same ID and the changed values, and called db.Categories.Update. EF Core refused: The instance of entity type 'Category' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. A context allows one object per row. The simple fix is to change the object you loaded, with no Update call at all.

The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded. EF Core expected its UPDATE to change one row, and MySQL said none. I got this message three different ways while building an API, and each needs a different answer. Telling them apart takes one look at the SQL.

dotnet ef database update printed The model for context 'InventoryDbContext' has pending changes., then No migrations were applied, then Done., and exited with code 0. My new column wasn't in MySQL. That message has two possible causes, and with this provider it won't stop a script, so it's easy to miss.

An UPDATE in MySQL stopped with ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Safe-updates mode refused it because nothing limited it to particular rows. Usually that's the mode doing what it's for, and the right fix is a better WHERE.

An INSERT into MySQL stopped with ERROR 1364 (HY000): Field 'CreatedAtUtc' doesn't have a default value. The column needs a value, the statement didn't give one, and MySQL has nothing to fall back on. The fix is to send the value, or give the column a default.

I sent a product price of 9.999 to my API, and it answered 201 Created with a price of 10.00. No error, no warning: MySQL rounded the amount to fit its DECIMAL(12,2) column, even in strict mode, and raised only a note that EF Core never shows. For money, silently changing the number isn't acceptable, so the API now refuses it.