287 posts tagged with "MySQL"

A long category name made my API answer 500, with Data too long for column 'Name' at row 1 in the log. MySQL was right to refuse it: the column is varchar(100), and the name had 101 characters. The problem was that the request got as far as MySQL at all. A validation attribute stops it first, and the client gets an answer it can act on.

Right after I added a property to an EF Core entity, every request that touched that table answered 500, and the log said MySqlException: Unknown column 'p.Version' in 'field list'. The code built without a problem, because the C# was fine. The table simply didn't have that column yet: changing the class doesn't change the database until a migration does.

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.