EF Core Entity with Primary Key but without Clustered Index

For Entity Framework, when we have a primary Key on the Entity, EF always generates a clustered index.

public class TestEntity
{
    [key]
    public int NaturalKeyId;

    public string Field2;
    public string Field3;
}

The NaturalKeyId field will form a clustered index.

Is there a way to retain it as the (unique) primary key, but not have it as a clustered index? Before I get trolled, I agree that it is generally better to have a clustered index on a monotonously increasing (surrogate) Id field. However in a specific case, all update operations are always using the NaturalKey (there is no convenient Id passed in for the Update. Updates are always triggered by external systems that only know the Natural Key) and I want to avoid (if possible) the overhead of having to first read the Entity via the natural key before being able to update / delete it.

I cannot convert the Natural Key itself as the clustered Index as the key values are essentially random and I will face Page Splits for Inserts.

Edit: I already have a working solution where after running the migration, I manually recreated the table in the DB with a unique index on NaturalKeyId instead of Clustered Index.

Solution 1:

For Microsoft SQL Server, you can use the fluent API to configure the primary key as non-clustered:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    modelBuilder.Entity<TestEntity>().HasKey(e => e.NaturalKeyId).IsClustered(false);
}

Microsoft SQL Server Database Provider - Indexes - EF Core | Microsoft Docs

EF Core Entity with Primary Key but without Clustered Index

Solution 1:

Related

Recent Posts