I need to add a column to a table if that column doesn't exist. Reason: in some database we have such column and in some - not. I've added field to my model and create migration:
public override void Up()
{
AddColumn("dbo.NavFilters", "Promo", c => c.String(maxLength: 100, nullable:true));
}
public override void Down()
{
DropColumn("dbo.NavFilters", "Promo");
}
How can I check is the column exists?
I have worked on creating a custom migration method, AddColumnIfNotExists
You need a custom MigrationOperation class:
public class AddColumnIfNotExistsOperation : MigrationOperation
{
public readonly string Table;
public readonly string Name;
public readonly ColumnModel ColumnModel;
public AddColumnIfNotExistsOperation(string table, string name, Func<ColumnBuilder, ColumnModel> columnAction, object anonymousArguments) : base(anonymousArguments)
{
ArgumentValidator.CheckForEmptyArgument(table, nameof(table));
ArgumentValidator.CheckForEmptyArgument(name, nameof(name));
ArgumentValidator.CheckForNullArgument(columnAction, nameof(columnAction));
Table = table;
Name = name;
ColumnModel = columnAction(new ColumnBuilder());
ColumnModel.Name = name;
}
public override bool IsDestructiveChange => false;
public override MigrationOperation Inverse => new DropColumnOperation(Table, Name, removedAnnotations: ColumnModel.Annotations.ToDictionary(s => s.Key,s => (object)s.Value) , anonymousArguments: null);
}
You also need a custom SqlGenerator class:
public class AddColumnIfNotExistsSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(MigrationOperation migrationOperation)
{
var operation = migrationOperation as AddColumnIfNotExistsOperation;
if (operation == null) return;
using (var writer = Writer())
{
writer.WriteLine("IF NOT EXISTS(SELECT 1 FROM sys.columns");
writer.WriteLine($"WHERE Name = N'{operation.Name}' AND Object_ID = Object_ID(N'{Name(operation.Table)}'))");
writer.WriteLine("BEGIN");
writer.WriteLine("ALTER TABLE ");
writer.WriteLine(Name(operation.Table));
writer.Write(" ADD ");
var column = operation.ColumnModel;
Generate(column, writer);
if (column.IsNullable != null
&& !column.IsNullable.Value
&& (column.DefaultValue == null)
&& (string.IsNullOrWhiteSpace(column.DefaultValueSql))
&& !column.IsIdentity
&& !column.IsTimestamp
&& !column.StoreType.EqualsIgnoreCase("rowversion")
&& !column.StoreType.EqualsIgnoreCase("timestamp"))
{
writer.Write(" DEFAULT ");
if (column.Type == PrimitiveTypeKind.DateTime)
{
writer.Write(Generate(DateTime.Parse("1900-01-01 00:00:00", CultureInfo.InvariantCulture)));
}
else
{
writer.Write(Generate((dynamic)column.ClrDefaultValue));
}
}
writer.WriteLine("END");
Statement(writer);
}
}
}
And an Extension Method to give you your "AddColumnIfNotExists" function:
public static class MigrationExtensions
{
public static void AddColumnIfNotExists(this DbMigration migration, string table, string name, Func<ColumnBuilder, ColumnModel> columnAction, object anonymousArguments = null)
{
((IDbMigration)migration)
.AddOperation(new AddColumnIfNotExistsOperation(table, name, columnAction, anonymousArguments));
}
}
In your EF Migrations Configuration file, you need to register the custom SQL generator:
[ExcludeFromCodeCoverage]
internal sealed class Configuration : DbMigrationsConfiguration<YourDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
// Register our custom generator
SetSqlGenerator("System.Data.SqlClient", new AddColumnIfNotExistsSqlGenerator());
}
}
And then you should be able to use it in place of AddColum like this (notice the this keyword):
[ExcludeFromCodeCoverage]
public partial class AddVersionAndChangeActivity : DbMigration
{
public override void Up()
{
this.AddColumnIfNotExists("dbo.Action", "VersionId", c => c.Guid(nullable: false));
AlterColumn("dbo.Action", "Activity", c => c.String(nullable: false, maxLength: 8000, unicode: false));
}
public override void Down()
{
AlterColumn("dbo.Action", "Activity", c => c.String(nullable: false, maxLength: 50));
DropColumn("dbo.Action", "VersionId");
}
}
And of course you want some tests for the operation:
[TestClass]
public class AddColumnIfNotExistsOperationTests
{
[TestMethod]
public void Can_get_and_set_table_and_column_info()
{
Func<ColumnBuilder, ColumnModel> action = c => c.Decimal(name: "T");
var addColumnOperation = new AddColumnIfNotExistsOperation("T", "C", action, null);
Assert.AreEqual("T", addColumnOperation.Table);
Assert.AreEqual("C", addColumnOperation.Name);
}
[TestMethod]
public void Inverse_should_produce_drop_column_operation()
{
Func<ColumnBuilder, ColumnModel> action = c => c.Decimal(name: "C", annotations: new Dictionary<string, AnnotationValues> { { "A1", new AnnotationValues(null, "V1") } });
var addColumnOperation = new AddColumnIfNotExistsOperation("T", "C", action, null);
var dropColumnOperation = (DropColumnOperation)addColumnOperation.Inverse;
Assert.AreEqual("C", dropColumnOperation.Name);
Assert.AreEqual("T", dropColumnOperation.Table);
Assert.AreEqual("V1", ((AnnotationValues)dropColumnOperation.RemovedAnnotations["A1"]).NewValue);
Assert.IsNull(((AnnotationValues)dropColumnOperation.RemovedAnnotations["A1"]).OldValue);
}
[TestMethod]
[ExpectedException(typeof(ArgumentNullException))]
public void Ctor_should_validate_preconditions_tableName()
{
Func<ColumnBuilder, ColumnModel> action = c => c.Decimal(name: "T");
// ReSharper disable once ObjectCreationAsStatement
new AddColumnIfNotExistsOperation(null, "T", action, null);
}
[TestMethod]
[ExpectedException(typeof(ArgumentNullException))]
public void Ctor_should_validate_preconditions_columnName()
{
Func<ColumnBuilder, ColumnModel> action = c => c.Decimal();
// ReSharper disable once ObjectCreationAsStatement
new AddColumnIfNotExistsOperation("T", null, action, null);
}
[TestMethod]
[ExpectedException(typeof(ArgumentNullException))]
public void Ctor_should_validate_preconditions_columnAction()
{
// ReSharper disable once ObjectCreationAsStatement
new AddColumnIfNotExistsOperation("T", "C", null, null);
}
}
And tests for the SQL Generator:
[TestClass]
public class AddColumnIfNotExistsSqlGeneratorTests
{
[TestMethod]
public void AddColumnIfNotExistsSqlGenerator_Generate_can_output_add_column_statement_for_GUID_and_uses_newid()
{
var migrationSqlGenerator = new AddColumnIfNotExistsSqlGenerator();
Func<ColumnBuilder, ColumnModel> action = c => c.Guid(nullable: false, identity: true, name: "Bar");
var addColumnOperation = new AddColumnIfNotExistsOperation("Foo", "bar", action, null);
var sql = string.Join(Environment.NewLine, migrationSqlGenerator.Generate(new[] {addColumnOperation}, "2005")
.Select(s => s.Sql));
Assert.IsTrue(sql.Contains("IF NOT EXISTS(SELECT 1 FROM sys.columns"));
Assert.IsTrue(sql.Contains("WHERE Name = N\'bar\' AND Object_ID = Object_ID(N\'[Foo]\'))"));
Assert.IsTrue(sql.Contains("BEGIN"));
Assert.IsTrue(sql.Contains("ALTER TABLE"));
Assert.IsTrue(sql.Contains("[Foo]"));
Assert.IsTrue(sql.Contains("ADD [bar] [uniqueidentifier] NOT NULL DEFAULT newsequentialid()END"));
}
}
Basic example with sql:
// add colun if not exists
migrationBuilder.Sql(
@"IF COL_LENGTH('schemaName.TableName', 'ColumnName') IS NULL
ALTER TABLE[TableName] ADD[ColumnName] int NULL
GO
");
No way to do it using standard DbMigration methods.
The best way is to include a "select fieldToCheck from myTable where 1=2" into a try catch then add the field if required (in catch).
The other way is to write a custom migration generator that extends the Migration generator (i.e. adding an AddColumnIfNotExists method). You can have a look here to see how to do it:
http://romiller.com/2013/02/27/ef6-writing-your-own-code-first-migration-operations/
I created the following extension method with the code from dgzornoza's answer:
public static OperationBuilder<SqlOperation> AddColumnIfNotExists(
this MigrationBuilder migrationBuilder,
string table,
string name,
string type,
bool nullable = false)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentException($"'{nameof(table)}' cannot be null or empty.", nameof(table));
}
if (string.IsNullOrEmpty(name))
{
throw new ArgumentException($"'{nameof(name)}' cannot be null or empty.", nameof(name));
}
return migrationBuilder.Sql(@$"IF COL_LENGTH('[dbo].[{table}]', '{name}') IS NULL
ALTER TABLE[{table}] ADD[{name}] {type} {(nullable ? "NULL" : "")}
GO
");
}
