遇到问题
在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 ( ) );
}
重新数据库迁移,成功!!