3

Is it possible to do any type of programmatic data transformation in Entity Framework 5 Code First migrations?

There is an Sql() method to execute queries, but it has return type void and I don't see any way to get the results of the queries I perform.

Example

I have table Recipe with one-to-many relationship to Ingredient. For various reasons I want to convert this to a Ingredients JSON string property instead. The only approach I can think of is something like this:

  • Create new column IngredientsJson
  • For each recipe, query its ingredients, construct a JSON string programmatically and insert into the new column.
  • Drop the old table Ingredient.
angularsen
  • 8,160
  • 1
  • 69
  • 83
  • 1
    I think I would do transformations in the Seed method, only use the new column(s/tables, ...) in the new code and leave the removal of the old database objects to the next migration. I have bad experiences with mixing DML and DDl in upgrades. Besides that, it is a safe feeling to have the old data available (though invisible) in a new release because the conversion may always have introduced unexpected bugs or even data loss. And one question: why do you want to _get the results of the queries_? – Gert Arnold Apr 07 '13 at 18:47
  • The idea was to get the results of an Sql() command where I queried the ingredients for a recipe, in order to programmatically convert it to a JSON array of ingredients. Thanks for the suggestion. One problem I see with your approach is that I need to make sure I have run Seed() at least once on all my DBs before migrating to the step that deletes the Ingredient table. I just see that as something that would be easy to forget, hence my desire to get the data transformation as part of the migration step. It just makes more sense and seems more robust in my head. – angularsen Apr 07 '13 at 19:07
  • Yeah I can see that. I'd just rather be safe than sorry, and JIRA is my memory. Maybe someone else has experience with your scenario. There are more aspects to this interesting question. I wonder if you should also provide a reverse conversion in the Down method? – Gert Arnold Apr 07 '13 at 19:16
  • Yes, I believe it would be a good idea to provide backwards data transformation as well. I do like the idea of maintaining a loss-less migration chain as long as it is possible. I'm not sure if there even are scenarios where that is not possible? – angularsen Apr 07 '13 at 19:32

1 Answers1

1

You should use db 'initializer' for what you want - and/ore 'Seed' of a sort (as to where to inject into the EF flow).

You can > take a look at this post with a customized < initializer - that performas both Db Create... and Migrate. It's not cut and paste solution, but mostly works (it was just a fast go at the problem, you'd need to adjust a bit, it has couple fixes below).

MigrateDatabaseToLatestVersion dose only the migration part - and you need seed-ing exposed - or manually wrap that part (the main point is in 'checks' done for different situations - i.e. when to 'engage' into migration - or seeding).

Migration should go first, and db 'creation' kind of doesn't make much sense, except for seeding.

You override Seed (you created) to put any db handling there - you have the DbContext exposed - and you can also call SqlQuery if needed.

How to create initializer to create and migrate mysql database?

Community
  • 1
  • 1
NSGaga-mostly-inactive
  • 14,052
  • 3
  • 41
  • 51
  • Thanks for your input. I think this suggestion is in line with @Gert Arnold's comments to my question and as far as I can tell the same challenges are still there to ensure the correct seeding is run after the appropriate migration steps. I honestly feel that Seed() should be used for seeding data, hence its name, and that data transformation is a natural part of the migration steps. It just makes more sense the way I think of it. I could be mistaken though. – angularsen Apr 08 '13 at 16:28
  • well it is actually - you can call it something else really :) What I want to say is, for mixing migration/seeding - you need a custom initializer. And that's the `place`, where it should be. And then (like I did it) and there - you can 'make up' your own 'StructureSeed' - it's all the same - that's no longer the 'Seed' anyway - you have a new initializer with its own rules. – NSGaga-mostly-inactive Apr 08 '13 at 16:31
  • Also - if using the `DbMigrator` you can't really change how it does that part. So migrator does - what's in the migration scripts (you can change things there, but not what you want). After that is 'you patching' the db structure - either tables/keys etc. - or the data - doesn't matter much as long as it's on the start. Keep in mind - just to do it only on the 'empty db' or no db scenarios. – NSGaga-mostly-inactive Apr 08 '13 at 16:34