In Entity Framework 6.1 (not Core), how can I use the IndexAttribute to define a clustered index?

Question

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.

Answer 1

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.