在实体框架6.1中，如何使用IndexAttribute定义聚簇索引？ [英] In Entity Framework 6.1, how can I use the IndexAttribute to define a clustered index?

问题描述

实体框架6.1（代码优先）增加了通过 IndexAttribute 添加索引的可能性。该属性接受一个参数，用于指定索引是否应该是聚簇的还是非聚集的。

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

$ b $因此，一旦我将 IndexAttribute 应用于 IsClustered = true ，我会收到一个错误，因为，由于密钥，已经有一个聚集索引

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

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

修改（2014-04-11）：另见 https： //entityframework.codeplex.com/workitem/2212 。

解决方案

只能有一个聚集索引表和默认情况下，Entity Framework / Sql Server将其放在主键上。

所以使用 IsClustered 属性在不是主键的索引上？好问题！ （+1）

这个类：

  public class Blog 
 {
 [Key（）] 
 public int Id {get;组; } 
 
 [MaxLength（256）] //需要限制集群索引的列大小
 public string Title {get;组; } 
 
 [Index（IdAndRating，IsClustered = true）] 
 public int Rating {get;组; } 
 
} 
  

将生成此迁移：

  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：假），
}）; 
 
 CreateIndex（dbo.Blogs，
 new [] {Rating，Title}，
 clustered：true，
 name：IdAndRating ）; 
 
} 
  

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

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

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.

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.

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.

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?

(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.)

Edit (2014-04-11): See also https://entityframework.codeplex.com/workitem/2212.

解决方案

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

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

This class:

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

}

will generate this migration:

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

Alter the migration to this:

    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

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.

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