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

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)?

The main problem with ms access is that there are not SQL statements to rename objects. The only way is to use external DLLs (usually ADOX).

So, if for you is OK you can use the IvanStoev suggestion. The only issue is that you have a column name that does not match with the property name (but is only an internal issue).

Another possibility is to add new column, copy data from the old to the new column and drop the old column.

Another possibility is that you use ADOX. You could use this source code

https://github.com/bubibubi/EntityFrameworkCore.Jet/blob/master/src/System.Data.Jet/AdoxWrapper.cs

If ADOX is not installed the app works (but rename raises an error). I'm using it to implement object renaming in EF core provider for Microsoft access.

The method to call is rename column.

In the provider for EF core (still in beta) it is implemented.