Entity Framework Code First, Clustered Indexes & Composite Keys

Question

I am attempting to add a non-unique, clustered index on one of the columns used by a composite key in a table using EF's Code First approach. So far, I have the following models. Note the IX_PackageTargetPackageId index attribute on the PackageTarget.PackageId property I am trying to add:

public class Package
{
    [Key]
    public int Id { get; set; }
    [Required]
    [Index("IX_PackageName", IsUnique = true)]
    public string Name { get; set; }
}

public class Target
{
    [Key]
    public int Id { get; set; }
    [Required]
    public string Value { get; set; }
}

public class PackageTarget
{
    [Key]
    [Column(Order = 0)]
    [Index("IX_PackageTargetPackageId", IsClustered = true)]
    public int PackageId { get; set; }

    [Key]
    [Column(Order = 1)]
    public int TargetId { get; set; }

    [ForeignKey("PackageId")]
    public virtual Package Package { get; set; }

    [ForeignKey("TargetId")]
    public virtual Target Target { get; set; }
}

I have the following migration which was automatically generated for me:

CreateTable(
    "dbo.PackageTargets",
    c => new
        {
            PackageId = c.Int(nullable: false),
            TargetId = c.Int(nullable: false),
        })
    .PrimaryKey(t => new { t.PackageId, t.TargetId })
    .ForeignKey("dbo.Packages", t => t.PackageId, cascadeDelete: true)
    .ForeignKey("dbo.Targets", t => t.TargetId, cascadeDelete: true)
    .Index(t => t.PackageId, clustered: true, name: "IX_PackageTargetPackageId")
    .Index(t => t.TargetId);

CreateTable(
    "dbo.Packages",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Name = c.String(nullable: false)
        })
    .PrimaryKey(t => t.Id)
    .Index(t => t.Name, unique: true, name: "IX_PackageName");

CreateTable(
    "dbo.Targets",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Value = c.String(nullable: false)
        })
    .PrimaryKey(t => t.Id);

The only amendment I have made to the migration is to add clustered: false to the PackageTargets primary key as I would like the IX_PackageTargetPackageId index to be clustered instead.

After the above amendment, I am still unable to run the migration as the following exception occurs:

Cannot create more than one clustered index on table 'dbo.PackageTargets'. 
Drop the existing clustered index 'PK_dbo.PackageTargets' before creating another.

From my migration, I cannot see other clustered indexes are present. Am I missing something?

score 1 · Accepted Answer · edited May 23 '17 at 10:28

You said:

attempting to add a non-unique, clustered index

And have this attribute on the PackageID property:

[Index("IX_PackageTargetPackageId", IsClustered = true)]

But you also have the [Key] attribute defined on multiple columns (since EF requires a primary key), creating a composite primary key. In EF code first it is not possible (without jumping through some hoops at least) to define a non-clustered primary key, so your composite primary key is always going to be the clustering key as well.

You cannot have two clustered indexes on a table (see this SO article: What do Clustered and Non clustered index actually mean?)

A clustered index physically re-orders the rows on the disk based on the key, whereas a non-clustered does not physically re-order. Since it is not possible to physically order the rows in two different ways at the same time, you can't specify two clustered indexes.

Unless you have done performance testing and see a real benefit in specifying the clustering key manually, I'd recommend just setting IsClustered to false and allowing EF to manage the clustering key.

trueboroda · Answer 2 · 2017-09-11T13:31:11.883

As DVK Said. In your case, EF trying to make two differnt clustered indexes. First clustered for PK and second for descibed in an index atribute. You must to make not clustered PK. Unfortunately, in a latest stable version of EF 6 it is not possibly or not obvious. Of course you alway can use Sql() method of DbMigration with pure SQL. But this solution not look elegant. A betta version of EF 6.2 have such possibility.

Install-Package EntityFramework -Version 6.2.0-beta1

Then via fluent api you got to define PK and your clustered index. This Exampple from my project.

 modelBuilder.Entity<OrderWaybill>()
    .HasKey(o => new { o.GUID, o.OrderDataCode }
            , config => config.IsClustered(false));
 modelBuilder.Entity<OrderWaybill>()
    .HasIndex("IX_PRIMARY_SELECT", IndexOptions.Clustered
                , ri=> ri.Property(x=>x.OrderDataCode)
                , ri => ri.Property(x => x.Number));

Entity Framework Code First, Clustered Indexes & Composite Keys

2 Answers2