45 posts tagged with "Database"

Part 1 connected a .NET C# Web API to MySQL, but the database is still empty. This part describes the inventory data as C# classes and lets EF Core migrations create the matching MySQL tables, with the column types, keys, indexes and constraints you'd otherwise write by hand. You'll read the SQL before it runs, apply it, change the model and add a second migration, and then check every table from inside MySQL.

Some rules apply to every property of a type: all money is decimal(12,2), every DateTime is UTC, every enum is stored by name. Writing those rules property by property is repetitive, and the next property someone adds will miss them. EF Core's ConfigureConventions sets each rule once for the whole model. This guide writes three such rules, proves each one leaves the model unchanged when it replaces per-property code, and shows how a single property can still be different.

One category has many products; each product belongs to one category. That's a one-to-many relationship, the most common shape in any database. EF Core can discover it from your classes alone, and it's worth knowing exactly what it decides for you before you configure anything. This guide models it in C#, shows the MySQL foreign key, index and delete rule EF Core creates by convention, and then configures the same relationship explicitly.

Whether a database column may be empty should be decided once, in your C# code, and enforced everywhere: by the compiler, by EF Core and by MySQL. With nullable reference types turned on, EF Core does exactly that: string? becomes a column that allows NULL, string one that doesn't. This guide shows the rule in a real migration, adds C#'s required modifier so the compiler catches missing values, and changes an existing column from nullable to required without breaking.

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.

Every EF Core rule you add, a table name, a maximum length, an index, a constraint, goes into OnModelCreating by default, and that method grows until nobody wants to touch it. IEntityTypeConfiguration<T> gives each entity its own small class instead. This guide moves a real configuration into such classes, registers them all with one line, and proves the move changed nothing in the database model.

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.