在 Entity Framework 6.1(非核心)中，如何使用 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 注释)，并且该主键始终创建为 集群 键.

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.

因此，一旦我将 IndexAttribute 与 IsClustered = true 一起应用，我就会收到一个错误，因为由于密钥的原因，已经存在 is 一个聚集索引.

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.

推荐答案

一张表只能有一个聚集索引，默认情况下Entity Framework/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(非核心)中，如何使用 IndexAttribute 定义聚集索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！