在Entity Framework 6.1（非Core）中，如何使用IndexAttribute定义聚簇索引？ [英] In Entity Framework 6.1 (not Core), how can I use the IndexAttribute to define a clustered index?

问题描述

Entity Framework 6.1（代码优先）增加了通过 IndexAttribute 添加索引的可能性。该属性采用一个参数来指定索引应该是聚簇还是非聚簇。

Entity Framework 6.1 (code-first) has added the possibility of adding indexes via the IndexAttribute. The attribute takes a parameter for specifying whether the index should be clustered or non-clustered.

同时，AFAIK，Entity Framework要求每个实体都有一个主键（使用 KeyAttribute 注释），并且该主键始终创建为 clustered 键。

At the same time, AFAIK, Entity Framework requires every entity to have a primary key (annotated with the KeyAttribute), and that primary key is always created as a clustered key.

因此，只要我用 IsClustered = true 应用 IndexAttribute ，我就会收到错误，因为，由于密钥，已经 聚集索引。

Therefore, as soon as I apply the IndexAttribute with IsClustered = true, I get an error because, due to the key, there already is a clustered index.

那么，我如何创建一个不是主键的聚簇索引 IndexAttribute ？是否可以使用 IndexAttribute 的 IsClustered 属性？

So, how can I create a clustered index that is not the primary key using the IndexAttribute? Is the IsClustered property of the IndexAttribute usable at all?

（对于更多上下文：我正在映射一个仅用于通过LINQ查询读取的表。我不需要实际插入，更新或删除该表中的实体。因此，我根本不需要主键。理想情况下，我想要一个没有主键的表，但是有一个非唯一的聚簇索引，可以优化读取。）

(For a little more context: I'm mapping a table that is only used for reading via LINQ queries. I do not need to actually insert, update, or delete entities from that table. Therefore, I don't need a primary key at all. Ideally, I'd like a table without a primary key, but with a non-unique, clustered index optimized for reading.)

编辑（2014-04-11）：另请参阅 https： //entityframework.codeplex.com/workitem/2212 。

推荐答案

一个聚集索引上只能有一个表，默认情况下，实体框架/ Sql Server将它放在主键上。

There can only be one clustered index on a table and by default Entity Framework/Sql Server puts it on the primary key.

那么使用什么是 IsClustered 属性索引不是主键？好问题！ （+1）

So what use is the IsClustered attribute on an index that is not the primary key? Good question! (+1)

此课程：

public class Blog
{
    [Key()]
    public int Id { get; set; }

    [MaxLength(256)]//Need to limit size of column for clustered indexes
    public string Title { get; set; }

    [Index("IdAndRating", IsClustered = true)]
    public int Rating { get; set; }

}

将产生此迁移：

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });
            .PrimaryKey(t => t.Id)
            .Index(t => t.Rating, clustered: true, name: "IdAndRating");
    }

改变迁移到此：

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });

        CreateIndex("dbo.Blogs", 
                    new[] { "Rating", "Title" }, 
                    clustered: true, 
                    name: "IdAndRating");

    }

这应该创建没有主键的表，但是其他列上的聚簇索引

And that should create your table without a primary key but with the clustered index on the other columns

编辑
在您不需要插入，更新或删除数据的场景中，你不需要一个完整的实体，你可以使用原始SQL查询填充类。您需要将自己的sql添加到迁移中以创建表，因为EF不会自动化它，但这意味着您可以根据需要创建表和索引。

EDIT In your scenario where you don't need to insert, update or delete data, you don't need a full blown entity, you could use raw sql queries to populate the classes. You would need to add your own sql to the migration to create the table because EF won't automate it, but that means you can create the table and index just as you want it.

这篇关于在Entity Framework 6.1（非Core）中，如何使用IndexAttribute定义聚簇索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！