Incorrect usage of spatial/fulltext/hash index and

问题:

I am getting Incorrect usage of spatial/fulltext/hash index and explicit index order this error while trying to login.

I am not using Entity Framework migration.

[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext()
        : base("DefaultConnection")
    {
    }

    static ApplicationDbContext()
    {
        // Set the database intializer which is run once during application start
        // This seeds the database with admin user credentials and admin role
        Database.SetInitializer<ApplicationDbContext>(new CreateDatabaseIfNotExists<ApplicationDbContext>());
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }


    public virtual DbSet<Category> Categories { get; set; }
}

This is my code to generate database automatically in MySQL. I don't have migration file.

回答1:

i haved the same problem, i fixed it with a custom class inherits from MySqlMigrationSqlGenerator, then i override the protected override MigrationStatement Generate ( CreateIndexOperation op ) method, then i create a new EFConfiguration class inherits from DbConfiguration and set the SetMigrationSqlGenerator with the new class. this is the code:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Sql;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.EntityFramework;
using MySql.Data.MySqlClient;

namespace customSqlGenerator.Contexto
{
    /// <summary>
    /// Inherit from MySqlMigrationSqlGenerator by fixing the Index generation method, set the default type in BTREE 
    /// <para>(the MySql manual says that in the SQL script syntax it is not necessary to specify it for the innoDB engine)</para>
    /// </summary>
    public class MyCustomMigrationSQLGenerator : MySqlMigrationSqlGenerator
    {
        private string TrimSchemaPrefix ( string table )
        {
            if ( table.StartsWith ( "dbo." ) )
                return table.Replace ( "dbo.", "" );
            return table;
        }

        protected override MigrationStatement Generate ( CreateIndexOperation op )
        {
            StringBuilder sb = new StringBuilder ( );

            sb = sb.Append ( "CREATE " );

            if ( op.IsUnique )
            {
                sb.Append ( "UNIQUE " );
            }

            sb.AppendFormat ( "index  `{0}` on `{1}` (", op.Name, TrimSchemaPrefix ( op.Table ) );
            sb.Append ( string.Join ( ",", op.Columns.Select ( c => "`" + c + "`" ) ) + ") " );

            return new MigrationStatement ( ) { Sql = sb.ToString ( ) };
        }
    }

    /// <summary>
    /// Inherit from DbConfiguration to set the new QSL script generator and set the conditions to work with MySQL
    /// </summary>
    public class MyCustomEFConfiguration : DbConfiguration
    {
        public MyCustomEFConfiguration ()
        {
            AddDependencyResolver ( new MySqlDependencyResolver ( ) );
            SetProviderFactory ( MySqlProviderInvariantName.ProviderName, new MySqlClientFactory ( ) );
            SetProviderServices ( MySqlProviderInvariantName.ProviderName, new MySqlProviderServices ( ) );
            SetDefaultConnectionFactory ( new MySqlConnectionFactory ( ) );
            SetMigrationSqlGenerator ( MySqlProviderInvariantName.ProviderName, () => new MyCustomMigrationSQLGenerator ( ) );
            SetManifestTokenResolver ( new MySqlManifestTokenResolver ( ) );
            SetHistoryContext ( MySqlProviderInvariantName.ProviderName, 
                ( existingConnection, defaultSchema ) => new MyCustomHistoryContext ( existingConnection, defaultSchema ) );
        }
    }

    /// <summary>
    /// Read and write the migration history of the database during the first code migrations. This class must be in the same assembly as the EF configuration
    /// </summary>
    public class MyCustomHistoryContext : HistoryContext
    {
        public MyCustomHistoryContext ( DbConnection existingConnection, string defaultSchema ) : base ( existingConnection, defaultSchema ) { }

        protected override void OnModelCreating ( System.Data.Entity.DbModelBuilder modelBuilder )
        {
            base.OnModelCreating ( modelBuilder );
            modelBuilder.Entity<HistoryRow> ( ).HasKey ( h => new { h.MigrationId } );
        }
    }

}

Then, on my DBContext i add attribute with the configuration, example:

[DbConfigurationType ( typeof ( MyCustomEFConfiguration ) )]
public class DataBaseContexto : IdentityDbContext<ApplicationUser, ApplicationRole, int, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{   
    public DataBaseContexto () : base ( "defaultConnection" )
    {           
        this.Configuration.LazyLoadingEnabled = true;
    }
}