c# - EF: Incorrect usage of spatial/fulltext/hash index and explicit index order

Question

I'm using Entity framework with my WEB Api project. I use code first migration.

The thing is: After making my intitial migration and trying to update database, I get this error

Incorrect usage of spatial/fulltext/hash index and explicit index order

Which is caused by this SQL command in update database:

create table `Articles` 
(
 `articleId` int not null  auto_increment ,
 `title` longtext not null ,
 `digest` longtext,
 `content` longtext not null ,
 `imgLink` longtext not null ,
 `releaseDate` datetime,
 `userId` int not null ,
  primary key ( `articleId`)
) engine=InnoDb auto_increment=0
  CREATE index  `IX_userId` on `Articles` (`userId` DESC) using HASH

The SQL command is generated from this code in migration:

CreateTable(
            "dbo.Articles",
            c => new
                {
                    articleId = c.Int(nullable: false, identity: true),
                    title = c.String(nullable: false, unicode: false),
                    digest = c.String(unicode: false),
                    content = c.String(nullable: false, unicode: false),
                    imgLink = c.String(nullable: false, unicode: false),
                    releaseDate = c.DateTime(precision: 0),
                    userId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.articleId)
            .ForeignKey("dbo.Users", t => t.userId, cascadeDelete: true)
            .Index(t => t.userId);

Seems like DESC and HASH on the index creation cause this error. Any ideas on how to change the generated sql Index creation so it goes with a simple indexation ? Or just simply bypass this error so my update-database can go through ? Thank you !

EDIT: added article class

public class Article
{
    public Article()
    {
        this.comments = new HashSet<Comment>();
    }

    [Key]
    public int articleId { get; set; }

    [Required]
    public string title { get; set; }

    public string digest { get; set; }

    [Required]
    public string content { get; set; }

    [Required]
    public string imgLink { get; set; }

    public DateTime? releaseDate { get; set; }

    // Clé étrangère (table User)
    public int userId { get; set; }

    // Auteur de l'article
    public virtual User user { get; set; }

    // Commentaires
    public virtual ICollection<Comment> comments { get; set; }
}

I'll add that DESC HASH on index is generated in output of every .Index() in migration file

Answer 1

Solved it.

In your migration file, replace .Index entries by sql commands like below

CreateTable(
        "dbo.Articles",
        c => new
            {
                articleId = c.Int(nullable: false, identity: true),
                title = c.String(nullable: false, unicode: false),
                digest = c.String(unicode: false),
                content = c.String(nullable: false, unicode: false),
                imgLink = c.String(nullable: false, unicode: false),
                releaseDate = c.DateTime(precision: 0),
                userId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.articleId)
        .ForeignKey("dbo.Users", t => t.userId, cascadeDelete: true)
        .Index(t => t.userId); // REMOVE THIS

Add the corresponding SQL command at the bottom of your Up() method (for every index)

Sql("CREATE index `IX_userId` on `Articles` (`userId` DESC)");

The problems I add then with DataReaders are MySQL connector related. MySQL connector doesn't support multiple active connections. To handle this, if you had this in your controller

public IEnumerable<Article> GetArticles()
{
    return db.Articles;
}

Now it should be

public IEnumerable<Article> GetArticles()
{
    return db.Articles.ToList(); // ToList() will manage the request to work with only ONE data reader, 
}

If you don't know how to convert your .Index() to SQL commands, just

update-database -verbose

and all the SQL commands will show