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!