Calling FluentMigrator methods inside Execute.WithConnection action

Question

Calling FluentMigrator's builder methods while inside the action that I pass to Execute.WithConnection causes a null reference exception to be thrown.

What I am trying to do is select some data so that I may manipulate it in c#, as that is easier than manipulating it in T-SQL, and use the result of my c# operations to update the data or insert new data (to be more specific, I need to pick one query string parameter out of a stored url string and insert it somewhere else).

The only way I see to select data within a migration is to use Execute.WithConnection and retrieve the data myself (FluentMigrator provides no helpers for selecting data), but if I try to use any fluent migrator expression in the action I pass to Execute.WithConnection a null reference exception is thrown.

Here is a boiled down version of my code:

[Migration(1)]
public class MyMigration : Migration 
{
  public void Up() 
  {
    Execute.WithConnection(CustomDml);
  }

  public void CustomDml(IDbConnection conn, IDbTransaction tran)
  {
    var db = new NPoco.Database(conn).SetTransaction(tran); // NPoco is a micro-ORM, a fork of PetaPoco
    var records = db.Fetch<Record>("-- some sql"); // this is immediately evaluated, no reader is left open
    foreach (var r in records) {
      var newValue = Manipulate(r.OriginalValue);
      Insert.IntoTable("NewRecords").Row(new { OriginalValueId = r.Id, NewValue = newValue }); // <-- this line causes the exception
    }
  }

  public void Down() {}
}

The line that calls Inser.IntoTable causes a null exception to be thrown from line 36 of FluentMigrator\Builders\Insert\InsertExpressionRoot.cs - it appears that the _context variable may be null at this point but I do not understand why this is. (when testing Create.Table, e.g., it occurs on line 49 of FluentMigrator\Builders\Create\CreateExpressionRoot.cs)

Any help would be appreciated. Perhaps there is disagreement on whether DML is appropriate in a migration, and I am open to suggestions, but this scenario has come up twice this week alone. For now I am simply performing the insert using my micro-ORM within the action rather than FluentMigrator and that does work, but it seems like what I am trying to do should work.

Heptavalent

Answer 1

When using the Execute.WithConnection expression all you get is the db connection and the transaction.

Using Execute.WithConnection creates an PerformDBOperationExpression expression. When processing the expression, a processor calls the Operation property (an example in the SqlServerProcessor) and the processor does not have a reference to the MigrationContext. But even if it did have access to the MigrationContext, when FluentMigrator has come to the processing stage, it is already too late. You would be trying to process expressions in a expression and at the moment FluentMigrator is not built to handle that type of nesting.

An alternative would be to make the connection string available in the migration context, see this issue: https://github.com/schambers/fluentmigrator/issues/240

Would that be a better approach?

Rocker

I don't think the connection string would be better, because using the parameters passed in Execute.WithConnetion allows me to run my operations inside the same transaction. I was thinking it was a limitation of nesting operation expressions, so I will simply stick with using alternate methods of dml inside WithConnection. Thank you. – Heptavalent

Yes, that would be helpful. Right now, Execute.WithConnection is executing out of order with my inserts, and I need to query the DB for some lookup values that are used to seed the test data. – Jeffiejeffrey