Migration pattern for logic too complex to cover it with SQL

Question

When you create migration in EF Core you can spice it up with arbitrary SQL query. Yet there is a limit to it -- maybe the migration is too complex in technical sense (i.e the SQL cannot handle it) or in readability sense (expressing the logic in SQL is too muddy). In short when migration logic can be only expressed in regular EF Core C# code with queries, filters, loops, ifs, etc.

Which leads to the question what are the patterns/solutions to handle/organize it?

So far I came up with idea of "freezing" type definitons (by copying them) of context, table models, and call of them let's say "FrozenVersion". Then on migration check if upcoming migration contains the "hot" step -- if yes, migrate it up to this step using FrozenVersion, execute special logic using this version, then disconnect from DB, switch to regular/current version of DB context, and models, connect back to DB, and migrate the rest using this version.

Clarification: this is general question about pattern which starts with "assume SQL is not enough for migration..." in the same sense as patterns for specific algorithms start "assume you have more data than RAM...".

Answer 1

The idea behind the MigrationBuilder class is IMHO the other way round:

1. It contains C# methods for the most common Schema changes as well as the most common data migrations,

2. and in case the standard methods of a MigrationBuilder cannot handle a migration, there is MigrationBuilder.Sql as the tool for handling any kind of complex migration.

In my experience, the number of cases which cannot be handled by these tools is quite rare. The SQL dialect of any DBMS I know allows to manipulate any kind of internal structure, all kind of things the standard MigrationBuilder methods don't provide.

Now you raised the question what to do when both of the former methods don't seem to be sufficient, and if it is possible to implement, for example, a complex data migration, without SQL, fully in C#, making use of tools like LINQ. Some reasons to justify this step are quite debatable, especially when it comes to "half-knowledge of SQL", because

• a certain knowledge of SQL is IMHO mandatory for anyone who calls themselves a professsional database programmer

• when the SQL gets "too muddy", I think chances are high it can be refactored into smaller, cleaner steps

• when a programmer has trouble to implement a complex migration with SQL in a clean way, they can always ask at a site like Stackoverflow or Codereview.Stackexchange

Still I think your question is certainly justified since I can imagine a few cases where it is indeed easier to implement something in C# than in SQL.

Maybe one needs to include a special library to make some complex technical calculation during a migration, maybe one wants to make use of things provided by the .Net framework or other existing code. For such cases, this Stackoverflow Q&A demonstrates how to get direct access to the database context during a migration, which is all one needs to implement arbitrary C# code which runs as part of migration step.

My priorities for designing a migration would always be (1) MigrationBuilder's standard methods, (2) the Sql method, and (3) custom C#. At least I would not lightheartly choose (3) over (2) just because to avoid SQL programming.

Answer 2

I think here you have to go back to the general practice of:

1. Testing your upgrades/migrations. ie, can v1 migrate to v2,v3,v4 without data loss
2. Checking the version you are upgrading is on that tested list before upgrading.

If you can't make the jump from v1 to v9, then error out and tell the user to go through whatever intermediate steps are required.

To clarify, if there is some DB transformation that's impossible to do with EF migrations. Don't try and force it into EF migrations with duplicate models and such.

Just do it with sql scripts as a special upgrade, and don't allow migrations that span the problem version (or any other untested upgrade) to run at all.

Second clarification from conversation in comments:

The question asker does not have a deployment or upgrade step, they are just overwriting the exe and running it, so the migrations get triggered, but no extra code.

What I'm suggesting is that you add some sort of deployment or patch process where you can execute arbitrary code when the software is updated.

If you have a CD pipeline to a shared service this is easy, just add more steps there.

If its desktop software, wrap the exe in a launcher which checks for updates and downloads and installs patches.

Once you have that extra step outside of just running the app, you can do all these annoying tasks easy peasy