CODEWITHSUNDEEP
indexingentity-framework-coreprimary-keyclustered-indexnon-clustered-index
Greg Harris
Greg Harris

Reputation: 31

Create nonclustered index with Entity Framework Core 5.0.4 with a GUID primary key

I have this entity class:

public class StatusCode
{
    public const String ClassName       = nameof(StatusCode);
    [Key]
    public Guid         UniqueID        { get; set; } // Primary Key
    public Char         Level           { get; set; } // R=Root, C=Code Group, I=Item
    public Guid         CodeGroup       { get; set; } // F Key - Which group of codes this code belongs to (self reference to this table)
    public String       Code            { get; set; } // Public short form code, normal public use in tabular reports
    public String       FullName        { get; set; } // Full public name, normal public stand alone display
    public String       Description     { get; set; } // Description displayed to explain this code to the public, typically a full sentance
    public String       PublicRemarks   { get; set; } // Longer public remark about this code, typically a few paragraphs
    public String       PrivateRemarks  { get; set; } // Internal use only remark, for display to administrators about the usage of this code
    public Boolean      AvailableYesNo  { get; set; } // Flag to show if this code is available for usage on new records
    public DateTime     AvailableFrom   { get; set; } // Date time that this code is available for first use
    public DateTime     AvailableTo     { get; set; } // Date time that this code is no longer available for use
}

With this migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
      String lLogHdr = $"{ClassName}.{HCG_Misc.CallingMethodName()} ";
      Console.WriteLine( $"{lLogHdr} - Start" );
      migrationBuilder.CreateTable(
          name: "StatusCode",
          columns: table => new
          {
            UniqueID       = table.Column<Guid    >( type: "uniqueidentifier", nullable: false ),
            Level          = table.Column<string  >( type: "nvarchar(1)"     , nullable: false ),
            CodeGroup      = table.Column<Guid    >( type: "uniqueidentifier", nullable: false ),
            Code           = table.Column<string  >( type: "nvarchar(450)"   , nullable: true  ),
            FullName       = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            Description    = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            PublicRemarks  = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            PrivateRemarks = table.Column<string  >( type: "nvarchar(max)"   , nullable: true  ),
            AvailableYesNo = table.Column<bool    >( type: "bit"             , nullable: false ),
            AvailableFrom  = table.Column<DateTime>( type: "datetime2"       , nullable: false ),
            AvailableTo    = table.Column<DateTime>( type: "datetime2"       , nullable: false )
          },
          constraints: table => { table.PrimaryKey("PK_StatusCode_UniqueID", x => x.UniqueID);
          });

      migrationBuilder.CreateIndex( name: "Index_StatusCode_Code"     , table: "StatusCode", column: "Code"      );
      migrationBuilder.CreateIndex( name: "Index_StatusCode_CodeGroup", table: "StatusCode", column: "CodeGroup" );
      Console.WriteLine( $"{lLogHdr} - Complete" );
}

When I run

PM> Update-Database

I get a database table with a clustered primary key index, this is nuts for a GUID index. What do I need to do to get a nonclustered index?

Upvotes: 3

Views: 2036

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205539

The principle in EF Core is that each database provider adds its specific configuration extension methods. You didn't say what database are you targeting, so assuming it is SqlServer, the configuration method that you need is called IsClustered and extends the key configuration (HasKey).

So you need to add the following

modelBuilder.Entity<StatusCode>()
    .HasKey(e => e.UniqueID)
    .IsClustered(false);

and regenerate the migration. Now it should contain something like this

table.PrimaryKey("PK_StatusCode_UniqueID", x => x.UniqueID)
    .Annotation("SqlServer:Clustered", false);

and when applying it will create non clustered PK.

Upvotes: 3

Related Questions