Is there a good way to extend the Code-First Migrations

Question

I am starting a new project that uses Entity Framework. I have researched my options for how to create the database and found Code-First Migrations make the most sense (see bottom if you need to know why). Code-First Migrations lets me drop down to arbitrary SQL meaning I still have full control. In practice I found that issue is that dropping down to SQL seems terribly repetitive for some of the common tasks.

For my purposes, I do not care that extensions in the migration be provider agnostic (the SQL I am in-lining is not). However, I am not really finding a good seam or extension point in the migrations framework for adding such things.

To give a concrete example, suppose I want to specify a RowGuid column for MS-SQL replication. Every occurance takes the form of

Sql(
    string.Format(
        "Alter Table {0} Alter Column {1} Add ROWGUIDCOL",
        table,
        column ));

So I write static methods to get rid of some of the redundancy

Sql( MigrationHelper.SetRowGuid( table, column );

-or-

MigrationHelper.SetRowGuid(Sql, table, column); //passing the Sql method

Possibly could make either of those extension methods on DbMigration, and access them by this. But still this looks out of place:

CreateTable(
    "dbo.CustomerDirectory",
     c => new
         {
             Uid = c.Int(nullable: false),
             CustomerUid = c.Int(nullable: false),
             Description = c.String(nullable: false, maxLength: 50, unicode: false),
             RowGuid = c.Guid(nullable: false),
         })
     .PrimaryKey(t => t.Uid)
     .ForeignKey("dbo.Customer", t => t.CustomerUid);

this.SetRowGuid( Sql, "dbo.CustomerDirectory", "RowGuid" );
//Custom method here because of desired naming convention of Constraint
this.SetDefaultConstraint( Sql, "dbo.CustomerDirectory", "''" ):

It is not terribly, but it still feels like a hack to me. I have I have to repeat the table name, and I need to make sure I get the generated column name right. I find the table name needs to be repeated a lot, but so do columns. Yet what I am really trying to do as add onto the table declaration that just happened where both table name and column names were all known.

I, however, could not find a good extension point for extending the fluent interface or otherwise extending the code first migrations in a way that feels consistent. Am I missing something? Has anyone founds a good way of doing this?

Some justification as to why I am in this situation:

I did not like the what seemed like a common solution of using common custom attributes solution to indicate non-mapping database for a few reasons, but most strongly because they are not automatically picked up by migrations meaning extra maintenance. Model-first solutions were out out because they does not give full control over the database. Database-First was appealing because of the control; however, it does does not have the out-of-the-box change management functionality Code-First Migrations provides. Thus, Code-First Migrations seemed to be a winner because [code-first] model-driven changes are automatic and it meant there would be only one thing to maintain.

Answer 1

I have found a solution though I am not sure if it is good. I had to go a little farther down the rabbit hole than I wanted to get it, and it is not really an extension point.

It allows me to write statements such as:

CreateTable(
    "dbo.CustomerDirectory",
     c => new
        {
            Uid = c.Int(nullable: false),
            CustomerUid = c.Int(nullable: false),
            Description = c.String(nullable: false, maxLength: 50, unicode: false),
            RowGuid = c.Guid(nullable: false),
        })
    .PrimaryKey(t => t.Uid)
    .ForeignKey("dbo.Customer", t => t.CustomerUid)
      //SqlValue is a custom static helper class
    .DefaultConstraint( t => t.Description, SqlValue.EmptyString)
      //This is a convention in the project
      //Equivalent to
      //  .DefaultConstraint( t => t.RowGuid, SqlValue.EmptyString)
      //  .RowGuid( t => t.RowGuid )
    .StandardRowGuid()
      //For one-offs
    .Sql( tableName => string.Format( "ALTER TABLE {0} ...", tableName" );

I do not like:

• The fact that I am reflecting on private members, and normally would not use such a solution
• That the lambda to select a column could return the wrong column name if the "name" optional parameter of the column definition was used.

I am only considering using it here because:

• We ship the EF assembly so we are sure the one used will have these members.
• A couple unit tests will tell us if a new version will break these.
• It is isolated to migrations.
• We have all the information we are reflecting to get, so if a new version does break this, we could put in place a hack to replace this functionality.

internal static class TableBuilderExtentions
{
    internal static TableBuilder<TColumns> Sql<TColumns>(
        this TableBuilder<TColumns> tableBuilder,
        Func<string, string> sql,
        bool suppressTransaction = false,
        object anonymousArguments = null)
    {
        string sqlStatement = sql(tableBuilder.GetTableName());

        DbMigration dbMigration = tableBuilder.GetDbMigration();
        Action<string, bool, object> executeSql = dbMigration.GetSqlMethod();

        executeSql(sqlStatement, suppressTransaction, anonymousArguments);

        return tableBuilder;
    }

    [Pure]
    private static DbMigration GetDbMigration<TColumns>(this TableBuilder<TColumns> tableBuilder)
    {
        var field = tableBuilder.GetType().GetField(
            "_migration", BindingFlags.NonPublic | BindingFlags.Instance);
        return (DbMigration)field.GetValue(tableBuilder);
    }

    /// <summary>
    ///   Caution: This implementation only works on single properties.
    ///   Also, coder may have specified the 'name' parameter which would make this invalid.
    /// </summary>
    private static string GetPropertyName<TColumns>(Expression<Func<TColumns, object>> someObject)
    {
        MemberExpression e = (MemberExpression)someObject.Body;

        return e.Member.Name;
    }

    [Pure]
    private static Action<string, bool, object> GetSqlMethod(this DbMigration migration)
    {
        MethodInfo methodInfo = typeof(DbMigration).GetMethod(
            "Sql", BindingFlags.NonPublic | BindingFlags.Instance);
        return (s, b, arg3) => methodInfo.Invoke(migration, new[] { s, b, arg3 });
    }

    [Pure]
    private static string GetTableName<TColumns>(this TableBuilder<TColumns> tableBuilder)
    {
        var field = tableBuilder.GetType().GetField(
            "_createTableOperation", BindingFlags.NonPublic | BindingFlags.Instance);

        var createTableOperation = (CreateTableOperation)field.GetValue(tableBuilder);
        return createTableOperation.Name;
    }
}