Non Clustered Primary Key Entity Framework Code First

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'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:

  1. Use [Key] attribute to decorate NLogId property
  2. 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");
    
  3. 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),
    

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

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