In our solution, C# files generated by .NET Core migrations became 69% of all lines of code which is hurting compile times and intelligence works only on computers with 64+ GM RAM, so we need to squash migrations and keep only one for each release we historically made, because our product is distributed using installer to customers and different customers might be on different versions and needs to upgrade.
According to Microsoft documentation I should delete current migrations and generate a new diff script between those database state.
https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli
It seems to me that with this approach I would loose all data changes inside the migration
migrationBuilder.Sql("UPDATE public.foo SET my_id = 'xy';");
So what is the correct approach to squash migration including data changes?
Basically, it is not recommended to run migrations from code in a production environment. With an Azure Service, e.g., I had a problem that migration was run when the application started, and not during pipeline deployment, which effectively killed some background jobs that ran before the migration and found unexpected DB schema.
Your problems are different, and it's not clear, how the application is deployed to clients. It seems it's not a build pipeline but some kind of distribution (installer?).
Anyway, with each release, you could start a script (that you have to write) that generates SQL from all your migrations. This will keep the Sql() directives. So you will have a single SQL migration script per release, that you will run during deployment/installation from your pipeline/installer. Once you have the script, you can squash your code-migrations like Microsoft tells you. So you will end up with a limited number of code-migrations till next release and an SQL-file per realise (which won't influence building times).
The script will be something like (in PS)
cd your_project_folder
$migrations = dotnet ef migrations list --context YourStorageContext
$out = your_out_file
foreach ($migration in $migrations)
{
dotnet ef migrations script $migration --idempotent --context YourStorageContext --output $out
}
You can initially feed it with "stop points", i.e. your releases = last migration in release, which will denote a new $out.
You could even do the deleting and squashing in the script, following the SQL generation.