Rename column (and preserve data) with Entity Framework (code first) using Jet Engine

Question

I am looking for a solution to rename a column of a table preserving the values using a Entity Framework code first migration with Jet Engine database provider (MS Access Database).

I have tried several approaches from which none of them have worked due to dealing with Jet Engine an MS Access DB.

Following are the details to my model changes and approaches I have already tried:

Model changes

(I shortened the changes to keep things clear):

    ' OLD properties
    ' Public Property WebServiceState As ServiceStateType?
    ' Public Property WebServiceRegistrationDate As DateTime?
    ' Public Property WebServiceEndOfTrialDate As DateTime?
    ' Public Property WebServiceExpirationDate As DateTime?

    ' NEW
    Public Property WebServiceState As ServiceState

Besides "WebService" there are other service types which all have the same properties (registration date, ...). Therefore I created a new class ServiceState which represents these properties.

Public Class ServiceState
    Public Property State As ServiceStateType?
    Public Property RegistrationDate As DateTime?
    Public Property EndOfTrialDate As DateTime?
    Public Property ServiceStopAtDate As DateTime?
End Class

Migration

On migration I must move the data to the new structure. I created a migration using PM-Console and "add-migration". This is the auto generated code of the up function:

    Public Overrides Sub Up()
        AddColumn("dbo.Services", "WebServiceState_State", Function(c) c.Int())
        AddColumn("dbo.Services", "WebServiceState_RegistrationDate", Function(c) c.DateTime())
        AddColumn("dbo.Services", "WebServiceState_EndOfTrialDate", Function(c) c.DateTime())
        AddColumn("dbo.Services", "WebServiceState_ServiceStopAtDate", Function(c) c.DateTime())

        DropColumn("dbo.Services", "WebServiceState")
        DropColumn("dbo.Services", "WebServiceRegistrationDate")
        DropColumn("dbo.Services", "WebServiceEndOfTrialDate")
        DropColumn("dbo.Services", "WebServiceExpirationDate")
    End Sub

With this code the data will be lost as there is no data transfer between old and new properties.

Approaches tried

To keep the data I tried the following approaches as found in How to rename a database column in Entity Framework 5 Code First migrations without losing data?.

1. Using RenameColumn

RenameColumn("dbo.Services", "WebServiceState", "WebServiceState_State")

This does not work. PM-Konsole reports

Cannot rename objects with Jet

2. Using SQL to make an update

Sql("Update dbo.Services SET [WebServiceState_State] = [WebServiceState]")

Does not work either. It throws an error on GenerteSqlStatementConcrete

3. Changing data in migration Up method using the database context

This is IMHO not an option. It leads to an inconsistent state as 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 error.

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

To make this work, I would have to disable model checking which I believe is not a good thing to do. (Reference: Change data in migration Up method - Entity Framework)

Question

How can I rename the columns / keep the data on migration based on the limitations I am dealing with as mentioned above (mainly Jet Engine due to MS Access database)?

Answer 1

In addition to my comment on bubi's answer, here is the code I finally used. Be sure that you are using JetEntityFrameworkProvider 1.2.7 or higher. Below SQL statements are not supported on migration (reference).

Public Overrides Sub Up()
    AddColumn("dbo.Services", "WebServiceState_State", Function(c) c.Int())
    AddColumn("dbo.Services", "WebServiceState_RegistrationDate", Function(c) c.DateTime())
    AddColumn("dbo.Services", "WebServiceState_EndOfTrialDate", Function(c) c.DateTime())
    AddColumn("dbo.Services", "WebServiceState_ServiceStopAtDate", Function(c) c.DateTime())

    Sql("UPDATE Services SET [WebServiceState_State] = [WebServiceState]")
    Sql("UPDATE Services SET [WebServiceState_RegistrationDate] = [WebServiceRegistrationDate]")
    Sql("UPDATE Services SET [WebServiceState_EndOfTrialDate] = [WebServiceEndOfTrialDate]")
    Sql("UPDATE Services SET [WebServiceState_ServiceStopAtDate] = [WebServiceExpirationDate]")

    DropColumn("dbo.Services", "WebServiceState")
    DropColumn("dbo.Services", "WebServiceRegistrationDate")
    DropColumn("dbo.Services", "WebServiceEndOfTrialDate")
    DropColumn("dbo.Services", "WebServiceExpirationDate")
End Sub