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