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.
Advertisement
Answer
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; } }