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:

Create table B through SQL query
Perform transformation over entire copy of data from A format to B format through SQL query
Put everything to B table through SQL query
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:

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?

Running the custom SQL should be no problem using MigrationBuilder.Sql(). Not sure how you can get a proxy to the webservices into the migration though. See also Custom Migrations Operations — Georg Patscheider
– Georg Patscheider, Commented Feb 5, 2018 at 15:36
No, this is not what Im looking. By custom here I mean really custom, like invoking Python/C++/C/C# library or making some other integration for transformation of data. — eocron
– eocron, Commented Feb 5, 2018 at 15:42
As far as I can see, you could open a separate database connection inside the Up method (not an EF context, but some more or less plain old connection), query data from A, process them and generate SQL insert statements for B that are included into the Migration via Sql method. Downside: you need a database downtime without external changes from the moment of Update-Database until the migration is fully applied - so pre-generating the migration SQL and executing it later is not a valid option. — grek40
– grek40, Commented Feb 13, 2018 at 18:01

Novakov · Accepted Answer · 2018-02-07 19:39:48Z

4

+100

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

answered Feb 7, 2018 at 19:39

Novakov

3,0951 gold badge19 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eocron Over a year ago

Actually, this is pretty good solution because cmd forces you to fix binaries like you fix SQL query in your migrations. Well, it can be pretty handy if one can write something like "precompiler" extension, which will be asociated with migration and invoked through "xp_cmdshell". Might actually write it.

Novakov Over a year ago

@eocron If you do, let us know - it will be useful :)

Collectives™ on Stack Overflow

Entity Framework data migration with custom logic?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related