create non clustered index on primary key entity framework 6.0

Question

I am aware of this, which states that it is not possible to create a primary key with non clustered index via code first. Is this still the case?

Ideally, I would like to specify via EntityTypeConfiguration, that my primary key (Guid) has a non-clustered index and there is another column (int) with a clustered index.

Possible duplicate of In Entity Framework 6.1, how can I use the IndexAttribute to define a clustered index? — Korayem
– Korayem, Commented Apr 18, 2016 at 19:20
you tagged the question entity-framework-6 so I presumed 6.1. I edited the title and removed the duplicate request. — Korayem
– Korayem, Commented Apr 19, 2016 at 8:35
For the benefit of searchers, I created a related post for EntityFramework Core, which also enables this - stackoverflow.com/questions/41004292/… — JJJCoder
– JJJCoder, Commented Dec 7, 2016 at 9:44

Alexander Polyankin · Accepted Answer · 2015-07-17 17:40:04Z

5

AFAIK this is not possible with EntityTypeConfiguration. However you can do this with Code-First migrations. Working example:

public class Product
{
    public Guid Id
    { get; set; }

    public int Price
    { get; set; }
}

class AppDbContext : DbContext
{
    public DbSet<Product> Products
    { get; set; }
}

public partial class InitialCreate : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Products",
            c => new
                {
                    Id = c.Guid(nullable: false),
                    Price = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id, clustered: false)
            .Index(t => t.Price, clustered: true);

    }

    public override void Down()
    {
        DropIndex("dbo.Products", new[] { "Price" });
        DropTable("dbo.Products");
    }
}

Result:

CREATE TABLE [dbo].[Products] (
    [Id]    UNIQUEIDENTIFIER NOT NULL,
    [Price] INT              NOT NULL,
    CONSTRAINT [PK_dbo.Products] PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

GO
CREATE CLUSTERED INDEX [IX_Price]
    ON [dbo].[Products]([Price] ASC);

answered Jul 17, 2015 at 17:40

Alexander Polyankin

1,9421 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cs0815 Over a year ago

Thanks. I have found something along those lines. I presume this is still the case then. I wait a bit longer before I accept.

Tyler Findlay · Accepted Answer · 2019-11-14 00:41:51Z

0

You can also do this with your OnModelCreating method like so:

modelBuilder.Entity(entityTypeName)
    .HasKey(nameof(ClassName.Id))
    .ForSqlServerIsClustered(false);

answered Nov 14, 2019 at 0:41

Tyler Findlay

6471 gold badge4 silver badges19 bronze badges

Collectives™ on Stack Overflow

create non clustered index on primary key entity framework 6.0

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related