EF6+MySql数据库迁移报错：Incorrect usage of spatial/fulltext/hash index and explicit index order

遇到问题

在EF框架做数据库迁移MySQL时出现了创建索引报错，可以生成迁移脚本并连接到数据库，但它不喜欢在创建索引时生成的SQL特别是“hash”。

实例:

CREATE index  `IX_Facility_ID` on `Contact.Address` (`Facility_ID` DESC) using HASH

错误:

MySql.Data.MySqlClient.MySqlException (0x80004005): Incorrect usage of spatial/fulltext/hash index and explicit index order

解决方案

创建一个继承mysqlmigrationsqlgenerator的类，并重写protected override MigrationStatement Generate（CreateIndexOperation op），然后在迁移的配置上添加：SetSqlGenerator（“MySql.Data.MySqlClient“，新建myMigrationSQLGenerator（））；             

代码：

public class myMigrationSQLGenerator : MySqlMigrationSqlGenerator
{
    private string TrimSchemaPrefix ( string table )
    {
        if ( table.StartsWith ( "dbo." ) )
            return table.Replace ( "dbo.", "" );
        return table;
    }

    protected override MigrationStatement Generate ( CreateIndexOperation op )
    {
        var u = new MigrationStatement ( );
        string unique = ( op.IsUnique ? "UNIQUE" : "" ), columns = "";
        foreach ( var col in op.Columns )
        {
            columns += ( $"`{col}` DESC{( op.Columns.IndexOf ( col ) < op.Columns.Count - 1 ? ", " : "" )}" );
        }
        u.Sql = $"CREATE {unique} INDEX `{op.Name}` ON `{TrimSchemaPrefix ( op.Table )}` ({columns}) USING BTREE";
        return u;
    }
}

这是关于Migrations\Configuration.cs中的代码:

    public Configuration ()
    {           
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator ( "MySql.Data.MySqlClient", new myMigrationSQLGenerator ( ) );
    }

重新数据库迁移，成功！！