Using Entity Framework 6, Custom Code-First Migrations & CSharpMigrationCodeGenerator

Question

I recently started using Entity Framework 6's code-first custom migrations. It's working well, but one thing I'd like to do is generate a pair of CreateIndex() and DropIndex() statements when attempting to rename an index, instead of using RenameIndex() like the default CSharpMigrationCodeGenerator wants to.

For example, I currently use data annotations in a fluent mapping to rename an index like this:

Property(x => x.TeacherId).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("IX_Students_TeacherId")));

The problem here is that by default EF6 wants to generate the following code when I add a new migration to capture this change to the model:

using System;
using System.Data.Entity.Migrations;

namespace MyApp.Migrations
{
    public partial class RenameIndexTest : DbMigration
    {
        public override void Up()
        {
            // BAD: [RenameIndex] will generate a "EXEC sp_rename" statement.
            RenameIndex(table: "dbo.Students", name: "IX_TeacherId", newName: "IX_Students_TeacherId");
        }

        public override void Down()
        {
            RenameIndex(table: "dbo.Students", name: "IX_Students_TeacherId", newName: "IX_TeacherId");
        }
    }
}

But what I really need EF6 to generate is this:

using System;
using System.Data.Entity.Migrations;

namespace MyApp.Migrations
{
    public partial class RenameIndexTest : DbMigration
    {
        public override void Up()
        {
            // GOOD: We generate separate SQL statements to drop & add the index.
            DropIndex(table: "dbo.Students", name: "IX_TeacherId");
            CreateIndex(table: "dbo.Students", name: "IX_Students_TeacherId", column: "TeacherId");
        }

        public override void Down()
        {
            DropIndex(table: "dbo.Students", name: "IX_Students_TeacherId");
            CreateIndex(table: "dbo.Students", name: "IX_TeacherId", column: "TeacherId");
        }
    }
}

Our data team has a hard requirement that developers use T-SQL DROP/CREATE statements when renaming indexes. Thus far, I haven't been able to find a way to override the behavior of the RenameIndex() statement, using a custom class that uses CSharpMigrationCodeGenerator as its base class, because the RenameIndexOperation class doesn't have any information about the column(s) an index has been created on.

This is as far as I've been able to get on my own:

namespace MyApp.Migrations
{
    internal class CustomCSharpMigrationCodeGenerator : CSharpMigrationCodeGenerator
    {
        protected override string Generate(IEnumerable<MigrationOperation> operations, string @namespace, string className)
        {
            var customizedOperations = new List<MigrationOperation>();

            foreach (var operation in operations)
            {
                if (operation is RenameIndexOperation)
                {
                    var renameIndexOperation = operation as RenameIndexOperation;

                    var dropIndexOperation = new DropIndexOperation(operation.AnonymousArguments)
                    {
                        Table = renameIndexOperation.Table,
                        Name = renameIndexOperation.Name
                    };

                    var createIndexOperation = new CreateIndexOperation(operation.AnonymousArguments)
                    {
                        Table = renameIndexOperation.Table,
                        Name = renameIndexOperation.NewName,

                        // HELP: How do I get this information about the existing index?
                        // HELP: How do I specify what columns the index should be created on?
                        IsUnique = false,
                        IsClustered = false
                    };

                    // Do not generate a RenameIndex() statement; instead, generate a pair of DropIndex() and CreateIndex() statements.
                    customizedOperations.Add(dropIndexOperation);
                    customizedOperations.Add(createIndexOperation);
                }
                else
                {
                    customizedOperations.Add(operation);
                }
            }

            return base.Generate(customizedOperations, @namespace, className);
        }
    }
}

Does this make sense? And more importantly, does anyone have any suggestions or ideas on how to proceed? Either way, thanks in advance!

You might be able to get close to that with a custom SqlServerMigrationSqlGenerator. See romiller.com/2012/01/16/… You can intercept the CreateIndexOperation() and write your own SQL. See here also stackoverflow.com/questions/8594431/… — Steve Greene
I'm trying to intercept RenameIndexOperation, not CreateIndexOperation. The index already exists before I add my migration to set it properly. Also, CreateIndexOperation inherits from IndexOperation, which exposes the Columns property I need. However, RenameIndexOperation inherits from MigrationOperation, which does not expose it. — Mass Dot Net
@SteveGreene: Sorry, I ran out of space above. Basically, I need a way to rewrite a RenameIndexOperation as a pair of DropIndexOperation and CreateIndexOperation operations. Simply intercepting my CreateIndexOperation won't work. — Mass Dot Net
@SteveGreene: That's exactly what I said in my original posting... my last code snippet even illustrates how I'm unable to do exactly that. — Mass Dot Net
It will get that info if you add it as anonymous argument from the Up() code, but one could argue your might as well just use Sql("DROP INDEX ..."); and Sql("CREATE INDEX ..."); — Steve Greene

Mass Dot Net Mass Dot Net · Accepted Answer · 2016-11-23T15:24:59

I'm closing this question out. I was never able to do exactly what I sought to... it wasn't a dealbreaker, I simply was hoping EF6 had some way of (easily) exerting control over the name of indexes being created.

IIRC, I did what @steve-greene suggested and manually specified the name of the index using the Sql() method.

Using Entity Framework 6, Custom Code-First Migrations & CSharpMigrationCodeGenerator

1 Answers