EF6防止不在外键上创建索引

嗯,我认为这可能是一个“如果你只有一把锤子……”有点像。

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
 {
   base.OnModelCreating(modelBuilder);

   // using System.Data.Entity.ModelConfiguration.Conventions;
   modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
 }

我不相信有一个简单的解决方案,但我知道你可以做什么:创建一个自定义迁移生成器。

迁移生成器是负责从迁移代码文件创建在数据库上运行的SQL脚本的组件。我假设您有基于屏幕截图的SQL Server。在这种情况下,您可以编写一个自定义sql生成器,该生成器只覆盖索引创建操作,这样,如果索引是非聚集的,则不会向脚本写入任何内容:

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

Configuration

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

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

然后,您可以在 配置 类如下:

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

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

添加迁移

您可以使用的其他属性 createIndexOperation Generate()

如果需要,还可以重写具有类型参数的Generate方法 DropCreateIndexOperation 但由于指数是以“存在时下降”的模式下降的,我认为这没有必要。

虽然上述代码示例似乎有效,但公平地说,并遵循一般的最佳实践和原则,您可能应该在if语句之后在两个生成器中都包含对基本方法的调用。

尝试使用后 CSharpMigrationCodeGenerator ForeignKeyIndexConvention .

public class ForeignKeyIndexConventionFix : ForeignKeyIndexConvention
{
    private const string Id = "Id";

    public override void Apply(AssociationType item, DbModel model)
    {
        if (item == null)
        {
            throw new ArgumentNullException(nameof(item));
        }
        if (item.Constraint == null)
        {
            return;
        }
        if (item.IsForeignKey)
        {
            if (IsPrimaryKeyColumn(item.Constraint))
            {
                return;
            }                
        }
        base.Apply(item, model);
    }

    private static bool IsPrimaryKeyColumn(ReferentialConstraint constraint)
    {
        IEnumerable<string> dependentColumns = constraint.ToProperties.Select(p => p.Name);

        if (dependentColumns.Count() == 1)
        {
            string dependentColum = dependentColumns.First();

            if (dependentColum.Equals(Id, StringComparison.OrdinalIgnoreCase))
            {
                return true;
            }
        }
        return false;
    }
}

然后覆盖您的 DbContext 类别:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
  modelBuilder.Conventions.Add<ForeignKeyIndexConventionFix>();
}

调试解决方案时出现问题- Console.Write 和 Debug.Write 我们并没有给出输出,所以我只是把跟踪放在临时位置的某个文本文件上。也许有更好的方法。。?

https://github.com/dotnet/ef6/blob/master/src/EntityFramework/ModelConfiguration/Conventions/Edm/Db/ForeignKeyIndexConvention.cs

使用EF Core 6.0,您需要从包含它的所有约定集中删除ForeignKeyIndexConvention。以下是一个可行的解决方案:

public class CustomSqlServerConventionSetBuilder : SqlServerConventionSetBuilder, IConventionSetBuilder
{
    public CustomSqlServerConventionSetBuilder(ProviderConventionSetBuilderDependencies dependencies, RelationalConventionSetBuilderDependencies relationalDependencies,
        ISqlGenerationHelper sqlGenerationHelper) : base(dependencies, relationalDependencies, sqlGenerationHelper)
    {
    }

    public override ConventionSet CreateConventionSet()
    {
        var cs = base.CreateConventionSet();

        //ForeignKeyAddedConventions
        var foreignKeyAddedConvention = cs.ForeignKeyAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyAddedConvention != null)
            cs.ForeignKeyAddedConventions.Remove(foreignKeyAddedConvention);

        //ForeignKeyRemovedConventions
        var foreignKeyRemovedConventions = cs.ForeignKeyRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyRemovedConventions != null)
            cs.ForeignKeyRemovedConventions.Remove(foreignKeyRemovedConventions);

        //EntityTypeBaseTypeChangedConventions
        var entityTypeBaseTypeChangedConventions = cs.EntityTypeBaseTypeChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (entityTypeBaseTypeChangedConventions != null)
            cs.EntityTypeBaseTypeChangedConventions.Remove(entityTypeBaseTypeChangedConventions);

        //KeyAddedConventions
        var keyAddedConventions = cs.KeyAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (keyAddedConventions != null)
            cs.KeyAddedConventions.Remove(keyAddedConventions);

        //KeyRemovedConventions
        var keyRemovedConventions = cs.KeyRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (keyRemovedConventions != null)
            cs.KeyRemovedConventions.Remove(keyRemovedConventions);

        //ForeignKeyPropertiesChangedConventions
        var foreignKeyPropertiesChangedConventions = cs.ForeignKeyPropertiesChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyPropertiesChangedConventions != null)
            cs.ForeignKeyPropertiesChangedConventions.Remove(foreignKeyPropertiesChangedConventions);

        //ForeignKeyUniquenessChangedConventions
        var foreignKeyUniquenessChangedConventions = cs.ForeignKeyUniquenessChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (foreignKeyUniquenessChangedConventions != null)
            cs.ForeignKeyUniquenessChangedConventions.Remove(foreignKeyUniquenessChangedConventions);

        //IndexAddedConventions
        var indexAddedConventions = cs.IndexAddedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexAddedConventions != null)
            cs.IndexAddedConventions.Remove(indexAddedConventions);

        //IndexRemovedConventions
        var indexRemovedConventions = cs.IndexRemovedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexRemovedConventions != null)
            cs.IndexRemovedConventions.Remove(indexRemovedConventions);

        //IndexUniquenessChangedConventions
        var indexUniquenessChangedConventions = cs.IndexUniquenessChangedConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (indexUniquenessChangedConventions != null)
            cs.IndexUniquenessChangedConventions.Remove(indexUniquenessChangedConventions);

        //ModelFinalizingConventions
        var modelFinalizingConventions = cs.ModelFinalizingConventions.FirstOrDefault(f => f is ForeignKeyIndexConvention);
        if (modelFinalizingConventions != null)
            cs.ModelFinalizingConventions.Remove(modelFinalizingConventions);
        return cs;
    }
}

并在DbContext配置中替换ConventionSetBuilder:

public partial class YourDbContext : DbContext
{
    ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        ...
        optionsBuilder.ReplaceService<IConventionSetBuilder, CustomSqlServerConventionSetBuilder>();
    }
}