55 posts tagged with "MySQL"

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.

dotnet ef database update is fine on your own machine, but a server shouldn't need the .NET SDK, the source code and the EF Core tools just to update its database. A migrations bundle packs your migrations and the code that applies them into one executable you can copy anywhere. This guide builds one, runs it, and then runs it the way Microsoft recommends for deployments, which taught me two things the hard way.

To use EF Core with MySQL you need a database provider, a NuGet package that teaches EF Core how to talk to MySQL. There are two serious choices: Oracle's MySql.EntityFrameworkCore and the community's Pomelo.EntityFrameworkCore.MySql. For a long time Pomelo was the usual answer. For EF Core 10, the deciding fact is simpler: only one of them has a release. Here's what I checked, from the package feed and the package files themselves, and what I learned building a .NET 10 Web API on Oracle's provider.

A migration is applied, and you need it undone: it was a mistake, or you want to test it again from the start. EF Core reverts migrations with the same command that applies them, dotnet ef database update, pointed at an earlier migration. This guide reverts one migration, then all of them, and shows the two things to check before you do it on a database you care about: whether Down can undo the data, and what it deletes.

Before applying, reverting or scripting migrations, you need two answers: which migrations exist in the code, and which ones this database already has. dotnet ef migrations list gives both in one list. This guide reads its output, including the (Pending) marker, gets the same list as JSON, lists without a database, and looks at the __EFMigrationsHistory table where EF Core keeps the record.

dotnet ef database update applies migrations straight to a database. Often you want the SQL instead: to read exactly what will run, to have a database administrator review it, or to apply it where the tools can't reach. dotnet ef migrations script generates that SQL from your migrations. This guide covers every form of the command with real scripts for MySQL, and shows why reading the SQL catches problems the C# migration hides.

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.