Update row data with a new value per row using fluentmigrator

c# fluent-migrator
shenku picture shenku · Sep 4, 2012 · Viewed 12.1k times · Source

I am using fluentmigrator to add a new column to a table. I then want to update each row in the table with a unique value for that column.

Currently when I use:

Update.Table("Foo").InSchema("dbo").Set(new { Bar = Bar.Generate() }).AllRows();

It gives the same value for all the rows.

How do I ensure it calls that method for each row?

Answer

Daniel Lee picture Daniel Lee · Sep 4, 2012

I'm not sure what Bar.Generate does but I am guessing it creates a GUID or unique id.

If so then you could use:

Execute.Sql("update dbo.Foo set Bar = NEWID()");

Or if you want sequential guids then you could use NEWSEQUENTIALID().

If you are adding a new column for this unique identier, then all you would need to do is specify the new column .AsGuid()

EDIT: FluentMigrator is a small fluent dsl and is not meant to cover a complicated case like this. There is no way (as far as I know) to do this with one sql UPDATE and therefore no easy way to do it with FluentMigrator. You'll have to get the row count for the table with ADO.NET or an ORM (Dapper/NHibernate) and then loop through each row and update the Bar column with the custom unique identifier. So if you have one million rows then you will have to make one million sql updates. If you can rewrite your Bar.Generate() method as an Sql function that is based on the NEWID() function like this or this then you could do it as one UPDATE statement and call it with FluentMigrator's Execute.Sql method.

You haven't mentioned which database you are working with. But some like Postgres have non-standard features that could help you.

Related questions

Execute FluentMigrator migrations from code

Are there any tutorials or example code for executing FluentMigrator migrations from within code? Some "Getting Started..." tutorial would be just awesome. All I was able to find was FluentMigrator.Tests (unit tests), inside FluentMigrator source, which are not as …

Read More
Is it possible to use fluent migrator in application_start?

I'm using fluent migrator to manage my database migrations, but what I'd like to do is have the migrations run at app start. The closest I have managed is this: public static void MigrateToLatest(string connectionString) { using (var announcer = new …

Read More
How to drop column with FluentMigrator?

I am using .Net4.5 and C#, I am working on one of database migrations using FluentMigrator. I am able to alter tables and add columns by using Alter.Table("Items").InSchema("Pricing") .AddColumn("CanBe").AsBoolean().NotNullable() However I need to …

Read More