2

Is it possible to transform data stored in a column using a calculation performed in c# during an Entity First database migration?

Currently, I have a column named Content of type nvarchar. I'd like to replace it with a column named ContentBinary of type varbinary, copying the content of each row in the process, but also transforming the content.

Specifically, I want to convert the string to a UTF-8 encoding and then compress it.

I know that the DbMigration class allows for transformation / data motion using the Sql*() methods, but those methods appear to require all the transformation logic to be in SQL. I think that would require the compression logic to be duplicated as a stored procedure in SQL Server, which would double the effort required and lead to the potential for inconsistencies over just using the custom c# compression routine directly.

I'd like to be able to iterate through all the rows, read each Content value, apply the transformation in C#, and then write it to ContentBinary.

I think this may need to happen as part of the migration transaction for consistency but also because only Content will exist before the migration and only ContentBinary will exist afterward. I assume that rules out opening a separate database connection during the migration. However, if there is a way to access the connection being used for the migration transaction, perhaps that would be enough.

Chris Pickford
  • 8,642
  • 5
  • 42
  • 73
twm
  • 1,448
  • 11
  • 19
  • 1
    I did something similar in the Seed() method of the migration. Add the column in the Up() method, then in the seed do if (db.Table.Any(t => t.NewColumn == null) { replace field where newfield is null and old field is not null } – Steve Greene Jan 23 '16 at 21:40
  • I'll probably do something similar (tweaked a bit since the old column will be removed from the table and entity). Seed() executes outside of the Up()/Down() transaction, right? That's the drawback. – twm Jan 24 '16 at 10:56

1 Answers1

0

you can use the AddOrUpdate method like the following

context.TableName.AddOrUpdate(x=> x.ID, collection);

where the collection is the table itself after apply transforming

if you provided some code in your question may be I could be more specific.

Hakan Fıstık
  • 16,800
  • 14
  • 110
  • 131
  • 1
    I thought that the context is not accessible during a migration, based on this: http://stackoverflow.com/a/24026126/1552934 Am I mistaken? – twm Feb 17 '16 at 15:35
  • Also, the documentation for AddOrUpdate() that you linked to says that it is useful for seeding, and my understanding is that seeding occurs after and outside of the migration transaction. – twm Feb 17 '16 at 15:45
  • sorry for mis-understanding you. I thought that you wanted to do this in seed, not in migration itself. – Hakan Fıstık Feb 17 '16 at 16:32
  • 1
    @twm any way I do not think that there is a way to do what you want in the migration itself. – Hakan Fıstık Feb 17 '16 at 16:33
  • 1
    That's OK, I probably will end up doing it in seed out of necessity and was trying to find a better way first. I was looking for a way to do it in the migration itself so that it's part of the transaction and has the advantages of ACID. It would also be cleaner to implement as part of the migration itself because the conversion is tied to a specific migration whereas seed gets run after every successful migration. – twm Feb 17 '16 at 16:44
  • I think you are completely right, in the way that you want to do this. I just got an idea that could (not sure) help you in doing this. there is Attribute which could be put on the property of the class (which represent column in table) https://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.schema.databasegeneratedattribute(v=vs.110).aspx – Hakan Fıstık Feb 17 '16 at 17:38
  • `[DatabaseGenerated(DatabaseGeneratedOption.Computed)]` here is the using of it. and you have to search more about this, It could (not sure again) be helpful. – Hakan Fıstık Feb 17 '16 at 17:42