EF6防止不在外键上创建索引 [英] EF6 preventing not to create Index on Foreign Key

问题描述

我首先使用 EF6 代码创建数据库。当我添加迁移和更新数据库时，默认情况下，总是为表中的每个外键创建非集群索引。

I'm using EF6 code first approach to create database. When i add migration and update database it always create Non-cluster Index for every foreign key in the table by default.

我的问题：是否有针对EF6的全局设置而不在外键上创建非集群索引？

My Question: Is there any global setting for EF6 to not create Non-Cluster indexon foreign key ?

我有搜索并找到以下解决方案

I have search and found the following solutions

解决方案1：在迁移之前删除索引行更新数据库

解决方案1不适合我，因为我有很多表，而我的 db 是已经创建。手动删除索引创建行需要很多时间。

Solution 1 not suits me because i have a lot of tables and my db is already created. Manually remove index creation line takes much much time.

此外，我还使用了流利的api 与这个问题有关？

Moreover i'm also using fluent api is there any option related to this issue ?

推荐答案

我不认为有一个简单的解决方案，但是我对您可以这样做：创建一个自定义迁移生成器。

I don't believe there is a simple solution to this, but I have an idea about what you could do: create a custom migration generator.

迁移生成器是负责创建从迁移代码文件在数据库上运行的SQL脚本的组件。我假设您有基于截图的SQL Server。在这种情况下，您可以编写一个自定义的sql生成器，该生成器将简单地覆盖索引创建操作，以便在非聚集索引的情况下，脚本中不会写入任何内容：

Migration generators are the components that are responsible for creating the SQL script that is run on the database from the migration code files. I assume you have SQL Server based on the screenshot. In this case, you can write a custom sql generator that simply overrides the index creation operation so that if the index is non-clustered, nothing is written to the script:

public class NoIndexGenerator : SqlServerMigrationSqlGenerator
{
  protected override void Generate(CreateIndexOperation createIndexOperation)
  {
    if (!createIndexOperation.IsClustered)
    {
      return;
    }
  }
}

然后您必须注册迁移的 Configuration 类中的组件：

Then you have to register this component in the Configuration class of the migration:

internal sealed class Configuration : DbMigrationsConfiguration<MyCtx>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;

    // Add this line to register the sql generator
    this.SetSqlGenerator("System.Data.SqlClient", new NoIndexGenerator());
  }
}

现在，如果您运行 Add -Migration ，您将拥有一个正常的迁移文件，其中包含CreateIndexOperation。但是，如果您运行 Update-Database ，将不会创建非聚集索引。如果运行 Update-Database -Script ，也可以检查此内容。生成的脚本不包含非聚集索引。

Now if you run Add-Migration, you'll have a normal migration file, with the CreateIndexOperation in it. But if you run Update-Database, the non-clustered indices will not be created. You can also check this if you run Update-Database -Script. The resulting script does not have the non-clustered indices.

如果需要，您可以在管道中更进一步，并创建一个自定义C＃迁移脚手架。它应用与sql生成器相同的逻辑：

If you want, you can go even higher up in the pipeline, and create a custom C# migration scaffolder. It applies the same logic as the sql generator:

internal class NoIndexMigrationCodeGenerator : CSharpMigrationCodeGenerator
{
  protected override void Generate(CreateIndexOperation createIndexOperation, IndentedTextWriter writer)
  {
    if (!createIndexOperation.IsClustered)
    {
      return;
    }
  }
}

然后，您可以注册在这样的 Configuration 类中：

Then, you can register it in the Configuration class like this:

internal sealed class Configuration : DbMigrationsConfiguration<MyCtx>
{
  public Configuration()
  {
    AutomaticMigrationsEnabled = false;

    // Add this line to register the C# code generator
    this.CodeGenerator = new NoIndexMigrationCodeGenerator();
  }
}

现在，如果您运行 Add-Migration ，CreateIndex操作也会从生成的迁移CS文件中消失。

Now, if you run Add-Migration, the CreateIndex operations will disappear from the generated migration cs files as well.

我可能会选择第二种解决方案（可能会使其他阅读您的代码的人感到困惑，以至于迁移cs文件中有CreateIndex操作，但SQL脚本中没有），但最终由您选择：）

I'd probably go with the second solution (it can be confusing for others reading your code to see that there are CreateIndex operations in the migration cs file, but not in the SQL scripts), but ultimately it is your choice :)

您可以使用 Generate（）方法的 createIndexOperation 参数的其他属性来实现更复杂的索引

You can play with other properties of the createIndexOperation parameter of the Generate() methods to implement more sophisticated index filtering, if you have to.

如果需要，还可以覆盖具有类型为 DropCreateIndexOperation ，但是由于索引以如果存在则丢弃模式删除，因此我认为这不是必需的。

If you want, you can also override the Generate methods that have a parameter of type DropCreateIndexOperation but since indices are dropped with a 'drop-if-exists' pattern, I don't think this is necessary.

编辑 >

虽然上面的代码示例似乎可行，但公平的是，遵循gen一般的最佳实践和原则，您可能应该在if语句之后的两个生成器中都包括对基本方法的调用。

While the above code samples seem to work, to be fair and follow general best-practices and principles, you should probably include calls to the base methods in both generators after the if statements.

这篇关于EF6防止不在外键上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！