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

c# entity-framework entity-framework-6 indexing

Question

The ability to add indexes using the code-first approach is now available in Entity Framework 6.1.IndexAttribute . The attribute accepts an argument that specifies whether or not the index should be clustered.

AFAIK, Object Framework also mandates that each entity have a primary key (annotated with theKeyAttribute ), and a clustered key is always generated for the primary key.

Consequently, as soon as I use theIndexAttribute with IsClustered = true I encounter an issue because the key already contains a clustered index.

So, how can I use the primary key to create a clustered index that is not the primary key?IndexAttribute ? Is theIsClustered possession of theIndexAttribute at all usable?

(To provide a bit more perspective, I'm using LINQ queries to map a table that is exclusively used for reading. Actually adding, updating, or removing entities from the table is not necessary. As a result, I don't even need a main key. In a perfect world, I'd want a table without a primary key but with a clustered index that is non-unique and optimized for reading.)

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

1
18
3/8/2018 7:25:29 PM

Accepted Answer

A table can only have one clustered index, which SQL Server/Entity Framework by default places on the primary key.

How then can theIsClustered an index that has an attribute other than the primary key? A good query! (+1)

the course:

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 cause this migration to occur

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

Modify the migration so that:

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

    }

Consequently, your table should be created without a primary key but with clustered indexes on the other fields.

EDIT You could utilize raw SQL requests to populate the classes in your scenario if you don't need to edit, update, or delete data or need a fully developed object. Because EF won't automate it, you would need to add your own sql to the migration to build the table, but you can create the table and index exactly how you wish.

9
4/4/2014 3:02:49 PM

Popular Answer

From SqlServerMigrationSqlGenerator, you can derive your own class and modify the pk creation there:

public class NonClusteredPrimaryKeySqlMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation)
    {
        addPrimaryKeyOperation.IsClustered = false;
        base.Generate(addPrimaryKeyOperation);
    }

    protected override void Generate(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
    {
        createTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(createTableOperation);
    }

    protected override void Generate(System.Data.Entity.Migrations.Model.MoveTableOperation moveTableOperation)
    {
        moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(moveTableOperation);
    }

full illustration here: https://entityframework.codeplex.com/workitem/2163



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow