Non Clustered Primary Key Entity Framework Code First

Question

Non Clustered Primary Key Entity Framework Code First

entity-framework-6 indexing

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

Satyajit

Popular Answer

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

Johnny Qian

View more on Stack Overflow

Larry · Accepted Answer · 5/23/2017 11:46:34 AM

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

Non Clustered Primary Key Entity Framework Code First

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related