Non Clustered Primary Key Entity Framework Code First

Question

In Entity Framework Code First approach, can we define the Primary Key as non-clustered index and a combination of few other fields as clustered index.

Thanks

Johnny Qian · Accepted Answer · 2017-07-23 14:00:34Z

18

EF 6.2 resolved this issue. Currently, it's in beta state, but it works.

First, upgrade your EF to 6.2:

Install-Package EntityFramework -Version 6.2.0-beta1 -Pre

Then, in the OnModelCreating method, set the IsClustered to false for the primary key:

modelBuilder.Entity<Receipt>().HasKey(r => r.RecId, config => config.IsClustered(false) );

answered Jul 23, 2017 at 14:00

Johnny Qian

7338 silver badges15 bronze badges

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

1 Comment

Edu Over a year ago

The version 6.2.0 is now final, no longer beta. This answer worked perfectly. I updated from 6.1.3 to 6.2.0 just to do this and had no problems. +1

Community · Accepted Answer · 2017-05-23 11:46:34Z

EntityTypeConfiguration does not provide a means of setting the Primary Key as a non-clustered index, but you can complete this by altering the initial migration used for table creation. There is an example here.

Here is an example of how to specify a clustered multiple-column index using attributes:

[Index("IX_ColumnOneTwo", 1, IsClustered = true)]
public int ColumnOne { get; set;}

[Index("IX_ColumnOneTwo", 2, IsClustered = true)]
public int ColumnTwo { get; set; }

and an example of how to complete this using model builder:

modelBuilder.Entity<ClassOne>() 
            .Property(t => t.ColumnOne) 
            .HasColumnAnnotation( 
                     "Index",  
                     new IndexAnnotation(new IndexAttribute("IX_ColumnOneTwo") { IsClustered = true }));
modelBuilder.Entity<ClassOne>() 
            .Property(t => t.ColumnTwo) 
            .HasColumnAnnotation( 
                     "Index",  
                     new IndexAnnotation(new IndexAttribute("IX_ColumnOneTwo") { IsClustered = true }));

Radenko Zec · Accepted Answer · 2016-09-28 11:09:02Z

5

There is a solution for Entity Framework Core Code First by overriding OnModelCreating in DbContext

  p.HasKey(b => b.ColumnId).ForSqlServerIsClustered(false);

This code will generate migration like this:

 table.PrimaryKey("PK_Columns", x => x.ColumnId)
                        .Annotation("SqlServer:Clustered", false);

answered Sep 28, 2016 at 11:09

Radenko Zec

7,6896 gold badges37 silver badges40 bronze badges

1 Comment

Jono Stewart Over a year ago

EF Core (using 7+ in .net 6, but might be available in earlier versions) you use: modelBuilder.Entity<EntityType>().HasKey(a => a.Id).IsClustered(false);

Alexei - check Codidact · Accepted Answer · 2019-07-02 09:00:50Z

Radenko Zec's answer is very useful also in EF Core. I will build upon it to provide a full solution for controlling the naming and also include an identity if needed. I will pick an example from one of my projects:

NLog

NLogId - Primary Key, non-clustered, identity
EnteredDate - Clustered index
<other columns>

In EF Core you need to:

Use [Key] attribute to decorate NLogId property

Use db context OnModelCreating override to customize your keys:

entity
    .HasKey(p => p.NlogId)
    .ForSqlServerIsClustered(false).HasName("PK_NLog");

entity
    .HasIndex(p => p.EnteredDate)
    .ForSqlServerIsClustered(true).HasName("IDX_NLog_EnteredDate");

Double check that identity code is generated (it is way harder to add identity afterwards):

migrationBuilder.CreateTable(
    name: "NLog",
    columns: table => new
    {
        NLogId = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),

Collectives™ on Stack Overflow

Non Clustered Primary Key Entity Framework Code First

4 Answers 4

1 Comment

Comments

1 Comment

NLog

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

NLog

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related