Create fulltext index within Entity Framework Coded Migrations

Question

TLDR; How do you add a full text index using Entity framework 5 coded migrations

I'm having issues adding a full text index to a database using Entity framework migrations. It needs to be there from the start so I'm attempting modifying the InitialCreate migration that was automatically generated to add it.

As there isn't a way to do it via the DbMigrations API I've resorted to running inline sql at the end of the 'Up' code.

Sql("create fulltext catalog AppNameCatalog;");
Sql("create fulltext index on Document (Data type column Extension) key index [PK_dbo.Document] on AppNameCatalog;");

When this runs everything gets created fine until it reaches this sql, then it throws the the sql error 'CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.'. Which is expected and working as designed.

Thankfully Sql() has an overload that allows you to run the sql outside the migration transaction. Awesome! I thought.

Sql("create fulltext catalog AppNameCatalog;", true);
Sql("create fulltext index on Document (Data type column Extension) key index [PK_dbo.Document] on AppNameCatalog;", true);

But low and behold modifying the code to do this (see above) results in a new timeout error 'Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.'

I've tried spitting out the sql and running it manually and it works fine. I've also diff'd the generated sql with and without running it outside a transaction and they are identical so it must be something in the way sql is executed.

Thanks in advance for any help!

Answer 1

I had a similar problem. My InitialCreate migration was creating a table and then attempting to add a full text index to that table, using the overloaded Sql() to indicate that it needs to execute outside the transaction. I was also getting a timeout error and I suspect it's due to a thread deadlock.

I could get it to work in some scenarios by using Sql() calls instead of CreateTable() and by merging the CREATE FULL TEXT CATALOG and CREATE FULL TEXT INDEX statements into a single Sql() call. However, this wasn't very reliable. Sometimes it would work and sometimes it would fail with the same timeout error.

The only reliable solution I found was to move the creation of the catalog and full text index into a separate migration.