EF:不正确使用空间/全文/哈希索引和显式索引顺序

问题描述

我在我的WEB Api项目中使用了Entity框架.我使用代码优先迁移.

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

这是由更新数据库中的此SQL命令引起的:

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

SQL命令是从迁移中的以下代码生成的:

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);

在创建索引时似乎出现DESC和HASH之类的错误.关于如何更改生成的sql索引创建的任何想法，使其与简单的索引一起使用?或者只是简单地绕过此错误，以便我的更新数据库可以通过?谢谢 ！

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 !

编辑:添加了文章类别

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; }
}

我要补充一点，在迁移文件中每个.Index()的输出中都会生成索引上的DESC HASH

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

推荐答案

已解决.

在您的迁移文件中，用如下所示的sql命令替换.Index条目

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

在Up()方法的底部(对于每个索引)添加相应的SQL命令

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

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

我随后在DataReaders中添加的问题与MySQL连接器有关. MySQL连接器不支持多个活动连接.要解决这个问题，如果您的控制器中有此

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;
}

现在应该是

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

如果您不知道如何将.Index()转换为SQL命令，只需

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

update-database -verbose

所有SQL命令都会显示

and all the SQL commands will show

这篇关于EF:不正确使用空间/全文/哈希索引和显式索引顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！