entity-framework-corejet-ef-provider

Locking issue when trying to alter column data in migration up

I am trying to add an sql statement to an up-method of a migration for my current project. The database is an Ms Access database. The migrations get applied during run-time. The situation is as follows: I have a base Initial-create migration, which in my case is assumed to be already applied. Due to the nature of this application we have a table A which contains some kind of a foreign key, but without any sql-constraints defined. This means the foreign-key relationship is designed via program code and not in sql means an foreign key relationship. The key is an string and if there is no foreign-element the value is empty. Now we want to add a new migration which enforces this relationship via sql-constraints. This works just fine via standard ef-core migration code, but the problem comes when the migration gets applied to a non empty database. The sql foreign key would need all the empty strings in table A to be null (otherwise we get an exception)

The seemingly easy solution was to add the following statement in the up-method of the new migration:

UPDATE A SET ForeignKeyColumn = NULL WHERE ForeignKeyColumn & \"\" = \"\""

But this results in the following exception:

System.Data.OleDb.OleDbException (0x80040E14): The database engine could not lock table 'A' because it is already in use by another person or process.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteNonQueryCore()
   at EntityFrameworkCore.Jet.Data.JetCommand.<>c.<ExecuteNonQuery>b__40_0(Int32 _, JetCommand command)
   at System.Linq.Enumerable.Aggregate[TSource,TAccumulate](IEnumerable`1 source, TAccumulate seed, Func`3 func)
   at EntityFrameworkCore.Jet.Data.JetCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at X.Infrastructure.Setup.Migrate(IFactory`1 pDatabaseContextFactory, String pDatabasePath)
   at X.COM.XCOMWrapper.Setup(ISettingsProvider pSettingsProvider)

However if we remove this sql statement from the migraition code and execute it as follows, before the call of context.Database.Migrate():

 var dbConnection = context.Database.GetDbConnection();
                     dbConnection.Open();
                     using (var transaction = dbConnection.BeginTransaction())
                     {
                         var updateForeignKeyReferences= dbConnection.CreateCommand();
                         updateForeignKeyReferences.CommandText = "UPDATE A SET ForeignKeyColumn = NULL WHERE ForeignKeyColumn & \"\" = \"\"";
                         updateForeignKeyReferences.ExecuteNonQuery();
                         transaction.Commit();
                     }
                     dbConnection.Close();

It works just fine. Is my approach of using the sql code in the up-method completly wrong? What are possible reasons for this? And most important, how can I fix this? The second approach is my current work-around for this problem but I fear that this means in the long run I can not use the migrations mechanism and have to go for a custom solution (or another framework). I would prefer to just stick with ef core.

Important: This application works with an legacy application and we have to insert the application history via sql code on the inital startup. For this we create an transaction and simple create the history table and insert the initially created table. This works just fine, and the transactions as well as the commands should all be closed. The table A is never touched by this functions.

Solution

  • Using migrationBuilder.Sql("UPDATE `A` SET `ForeignKeyColumn` = NULL WHERE `ForeignKeyColumn` = ''") is the correct procedure.

    It should execute fine.

    Unfortunately, there seems to be an issue, where Jet still holds a lock on the table used in the UPDATE command, when the CREATE INDEX statement is executed (that has been generated for your new navigation property and is part of the Up() migration method).

    This is only an issue, if both statements are executed inside the same transaction (which is the case for migrations by default). Otherwise, no lock is held and the CREATE INDEX statement succeeds.

    The simplest way to fix this issue, is to set the migrationBuilder.Sql() parameter suppressTransaction to true.

    This will execute the statement outside of the rest of the transaction, and not lock the table:

    protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        "UPDATE `A` SET `ForeignKeyColumn` = NULL WHERE `ForeignKeyColumn` = ''",
        suppressTransaction: true);
    
    migrationBuilder.CreateIndex(/* ... */);
    migrationBuilder.AddForeignKey(/* ... */);
}

    The other way, which is able to execute the UPDATE statement inside a transaction, is to execute the command in its own dedicated migration: