287 posts tagged with "MySQL"

A C# string has no length limit, so without being told otherwise, EF Core's MySQL provider gives each one the biggest text type MySQL has: longtext, up to 4 GB. That's wasteful for a SKU, and it gets in the way of indexes. This guide sets real maximum lengths so columns become varchar(n), applies the change to a table that already has data, and shows what happens when you index a string without a length.

EF Core names each table after your DbSet property: Products, Categories. On SQL Server or Windows that's harmless, but MySQL on Linux and in Docker compares table names case-sensitively, so products and Products are different tables and hand-written SQL breaks. This guide sets lowercase names with ToTable, shows the rename migration EF Core generates for an existing database, and the SQL MySQL actually runs.

What should happen to a category's products when the category is deleted? EF Core answers for you, unless you answer first: for a required relationship, its default is to delete the products too, silently. This guide shows that default happening in MySQL, switches it to Restrict so the database refuses instead, and shows the two different errors you get afterwards, one from MySQL and one from EF Core itself.

The most common query on a history table is "this item's entries, newest first". An index on just the item's Id finds the rows, but MySQL then has to sort them. An index on the Id and the time does both jobs. This guide creates one from EF Core's model, explains why the order of its columns decides what it can speed up, fixes the migration order MySQL needs, and uses EXPLAIN to prove the index is used.

Some values must never repeat: a product's SKU, a category's name, a user's email. Checking in code isn't enough, because two requests at the same moment can both pass the check. A unique index makes the database refuse the second one. This guide adds two from EF Core's model, applies them to MySQL 9.7, and shows what MySQL treats as a duplicate, which isn't always what you'd expect.

Validation in your API stops most bad data, but not data from a script, a second app, or a bug that skips the validation. A CHECK constraint makes the database itself refuse a row that breaks a rule, such as a negative price. This guide adds three to a products table from EF Core's model, applies them with a migration, and proves MySQL enforces them on both inserts and updates.

You save DateTime.UtcNow with EF Core, read it back from MySQL, and the Z that marks UTC is gone from your API's JSON. Worse, a DateTime.Now slipped in somewhere is stored as local time, hours away from everything else. This guide shows both problems with a real .NET C# Web API and MySQL 9.7, explains why they happen, and fixes them for every DateTime in the model with one small value converter.

Money belongs in a decimal, never a double, and the database column needs a precision you chose on purpose. This guide shows what MySQL column EF Core creates for a decimal when you don't configure it, how MySQL handles values that don't fit, and the three ways to set the precision: one property, an attribute, or one rule for every decimal in the model.

By default, EF Core saves a C# enum as its number: Sale becomes 2. That works, but anyone reading the table needs the C# code to know what 2 means, and reordering the enum silently changes the meaning of every old row. This guide stores enums as their names instead, first for one property and then for every enum at once, and handles the part most guides skip: the rows that already hold numbers.

Every .NET C# app that talks to MySQL starts with a connection string: one line that says where the server is, which database to use, who you are, and how to protect the connection. This guide builds one for Oracle's MySql.Data driver, which EF Core's MySql.EntityFrameworkCore provider also uses, explains each key with its documented default, and tests it with a small C# program that tells you exactly how it connected.