在实体框架编码的迁移中创建全文索引 [英] Create fulltext index within Entity Framework Coded Migrations

问题描述

TLDR；您如何使用Entity Framework 5编码迁移来添加全文索引

我在使用Entity Framework迁移向数据库添加全文索引时遇到问题。它需要从一开始就在那里，所以我尝试修改自动生成的InitialCreate迁移以添加它。

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.

由于无法通过DbMigrations API做到这一点，我不得不在 Up代码的末尾运行内联sql。

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;");

当此命令运行时，一切都会很好地创建，直到到达该sql，然后才会抛出sql错误 CREATE FULLTEXT CATALOG语句不能在用户事务内使用。'。这是预期的并按设计工作。

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.

感谢Sql（）具有一个重载，该重载使您可以在迁移事务之外运行sql。太棒了！我想。

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.'

我尝试吐出sql并手动运行它，它工作正常。我还对生成的sql与是否在事务外部运行进行了比较，并且它们是相同的，因此它们必须是执行sql的方式。

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.

谢谢

推荐答案

我也遇到了类似的问题。我的InitialCreate迁移是创建一个表，然后尝试使用重载的Sql（）指示它需要在事务外部执行，从而向该表添加全文索引。我也收到超时错误，我怀疑是由于线程死锁造成的。

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.

在某些情况下，我可以通过使用Sql（）调用而不是CreateTable使其正常工作（），并将CREATE FULL TEXT CATALOG和CREATE FULL TEXT INDEX语句合并到单个Sql（）调用中。但是，这不是很可靠。有时它会工作，有时会因相同的超时错误而失败。

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.

这篇关于在实体框架编码的迁移中创建全文索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！