Entity Framework data migration with custom logic?

Question

Suppose, I want to replace table A with table B and migrate all data from one to another, so I do:

1. Create table B through SQL query
2. Perform transformation over entire copy of data from A format to B format through SQL query
3. Put everything to B table through SQL query
4. Delete table A through SQL query

The problem is, sometimes you need to break transaction and do non-transactional transform from A format to B format, which can even involve calls to different services (for example, new geo-political status of object from A, or different serialization contract of fields from A, 7zip it from A to B or whatever you desire to change about data in A).

So, the question is, how to accomplish step 2 through EF in any desirable way:

2. Perform transformation over entire copy of data from A format to B format through "black box"

By that I mean not breaking concept of EF migration files, and providing me with something like "Main" method as entry point for my migration step. Any suggestions?

Answer 1

Unfortunately it's not possible with Entity Framework. Every operation that is available in migrations is transformed to SQL operations that are later invoked. (By operating this way EF allows you to script whole migration process to SQL file and run it in e.g. SQL Server Management Studio).

Because SQL generation is separated from invoking it, it's not possible to execute custom C#/Python/anything non-SQL.

As migrations allows only features provided by SQL Server (or different DB if supported) you can use features like CLR Assemblies or xp_cmdshell which are not the most straightforward things to use but that way it is possible to execute almost any migration code