3

I am looking for an appropriate way to expand an existing database, based on entity framework 6 code first implementation, and adding unique values to the new field for every existing row in the table.

So far I have created a migration which adds the new field.

The Up method looks like this:

Public Overrides Sub Up()
    AddColumn("dbo.Customers", "UniqueCode", Function(c) c.String(unicode:=False))
End Sub

I am stuck at the point where the new field should be filled with a unique (calculated) value. To keep it simple, let's say every existing row in the database should be assigned a GUID upon the migration.

Using an SQL statement like this would update all rows with the same GUID. But I need it to be unique for every row.

Sql("Update dbo.Customers SET UniqueCode = '" & Guid.NewGuid().ToString)

Using a foreach in the Up method seems kind of wrong... What is best practice in this case?

In addition: The database I am using is access, so I can't use newid() or random(). The GUID is meant to be a dummy for a programmatically calculated value. It will be a hashed value of some other attributes of the customer. So it must be calculated and updated with migration.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Maddin
  • 298
  • 3
  • 11
  • Can the new value be calculated server-side, or does it require application code to calculate? If it is possible to do server-side, you could script in and call a stored proc to calculate and update the data during `Up` after the field is added. – Bradley Uffner Aug 10 '17 at 15:00
  • Not sure if I fully understand your question. The new value is calculated from a set of existing values from the database customer record which are then hashed with SHA256 algo. No user input required. – Maddin Aug 10 '17 at 15:08
  • I'm asking if you could calculate and update the new field entirely within a [stored proc](https://stackoverflow.com/questions/3287545/how-do-i-make-a-stored-procedure-in-ms-access), or if you actually need to programatically calculate it, based on data not in the database, or using methods not supported by the database server. If you can do it in a proc, then add the proc and call it, in `Up` after the line where you add the column. – Bradley Uffner Aug 10 '17 at 15:11
  • Thanks for clarifying. I can't do it in a proc, because calculation is based on methods not supported by the database server. – Maddin Aug 10 '17 at 15:16
  • I edited your question to add a tag for ms-access, since that will affect possible answers. – Bradley Uffner Aug 10 '17 at 15:17
  • 1
    Based on the limitations you are dealing with, I think that looping through the data and updating row by row is going to be your only option. – Bradley Uffner Aug 10 '17 at 15:18

2 Answers2

2

I have found the following approach to be the best fit for my situation. I recommend splitting the database and data changes in 2 migrations, which allows you to use the framework for all changes and be fully up and down compatible.

Here the details to my approach:

Following Bradley Uffner's advice I loop through the data and update it row for row. But, doing this within one and the same migration, throws an error:

The model backing the 'DbContext' context has changed since the database was created. Consider using Code First Migrations to update the database.

Using a DbContext in the middle of it's migration leads to an inconsistent state. The model is already in its after migration state, but the database still has the before migration state in the tables. Model and database do not match, which leads to the above error. To make this work, I would have to disable model checking.

Reference: Change data in migration Up method - Entity Framework

In the above thread I found the suggestion to separate database changes from data changes. That's exactly what I did.

I created one migration to update the database

    Public Overrides Sub Up()
        AddColumn("dbo.Customers", "WebCode", Function(c) c.String(unicode := false))
    End Sub

    Public Overrides Sub Down()
        DropColumn("dbo.Customers", "WebCode")
    End Sub

Run the Update-Database command. And then create a second migration to make the data changes. The Up and Down methods will be empty upon creation. Here is the code I use to update the data row by row.

    Public Overrides Sub Up()
        Dim DbContext As New Data.DbContext

        For Each customer In DbContext.Customers.Where(Function(x) String.IsNullOrEmpty(x.WebCode))
            customer.WebCode = GetWebCode(customer)
        Next

        DbContext.SaveChanges()
    End Sub

    Public Overrides Sub Down()
        Dim DbContext As New Data.DbContext

        For Each customer In DbContext.Customers
            customer.WebCode = Nothing
        Next

        MyDbContext.SaveChanges()
    End Sub

Some might argue using a simple SQL in the Down method like

SQL("Update dbo.Customers SET WebCode = NULL")

is more efficient. I have tried that, but encountered an error at JetEntityFrameworkProvider.JetMigrationSqlGenerator.GenerateSqlStatmentConcrete which I was not able to locate and fix. It made Visual Studio crash.

Maddin
  • 298
  • 3
  • 11
1

If your database server is the SQL Server, you can use the builtin newid() function that generates GUIDs at the database server

https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql

The query would be 

Update dbo.Customers SET UniqueCode = newid()

In case of the SQLite database, use the random() function.

Wiktor Zychla
  • 47,367
  • 6
  • 74
  • 106
  • THX. I've added some additional info to my initial question. I know of these functions, but can't use them. I am looking for a way to update every row with a programmatic calculated value. – Maddin Aug 10 '17 at 14:29
  • @Maddin: next time remember to include such critical information to your question before someone bothers answering. Neither I knew you know these functions nor I knew you are using the Jet engine. – Wiktor Zychla Aug 10 '17 at 16:22