EF Core 3 Clustered indexing using fluent API not working

Question

I have a .Net core 3.1 API with EF core code first migrations enabled where I am trying to add a clustered index on a non PK column.

I have a Vehicle entity:

public class Vehicle 
{
    // PK and has clustered index by default
    [Key]
    public int Id { get; set; }

    // FK and needs to be clustered
    public long CompanyId { get; set; }
    [ForeignKey(nameof(CompanyId))]
    public virtual Company Company { get; set; }
}

I created a code first migration for the above model, then updated the database. At this point the table has PK as Id with clustered index and CompanyId just as FK.

To remove clustered index from Id and to add Clustered Index on CompanyId I wrote the following in Fluent API in the OnModelCreating() method

// Remove ClusteredIndex from PK
modelBuilder.Entity<Vehicle>().HasIndex(m => m.Id).IsUnique(true).IsClustered(false);
// Add clustered index on CompanyId
modelBuilder.Entity<Vehicle>().HasIndex(m => m.CompanyId).IsUnique(false).IsClustered(true);

When I run the API, I can see that the above ccode does execute but it has no effect on the SQL DB and does not change the indexing as desired.

What am I missing here? How do I add a clustered index on CompanyId column?

bit · Accepted Answer · 2020-06-30T03:35:54.063

Used good old SQL inside the Migrations to fix this.

First created an extension method for MigrationBuilder:

public static void CreateNonPKClusteredIndex(this MigrationBuilder migrationBuilder,
    string tableNameWithSchema,
    string pkConstraintName, string pkColumnName,
    string nonClusteredIndexName, string clusteredColumnName)
{
    // Change PK clustered index to non clustered
    migrationBuilder.Sql($"ALTER TABLE {tableNameWithSchema} DROP CONSTRAINT {pkConstraintName}");
    migrationBuilder.Sql($"ALTER TABLE {tableNameWithSchema} ADD CONSTRAINT {pkConstraintName} PRIMARY KEY NONCLUSTERED({pkColumnName})");

    // Update CompanyId index to clustered index
    migrationBuilder.Sql($"Drop Index {nonClusteredIndexName} on {tableNameWithSchema}");
    migrationBuilder.Sql($"CREATE CLUSTERED INDEX[{nonClusteredIndexName}] ON {tableNameWithSchema}({clusteredColumnName} ASC)");
}

Then simply called it inside the Up() in the respective migration:

migrationBuilder.CreateNonPKClusteredIndex("Core.Vehicles",
    "PK_Vehicles", "Id",
    "IX_Vehicles_CompanyId", "CompanyId");

Result:

EF Core 3 Clustered indexing using fluent API not working

1 Answers1