EF Core - composite primary key removes one index

c# ef-migrations entity-framework entity-framework-core

Question

I have this entity:

[Table("order")]
public class Order
{
    [Key]
    [Column("id")]
    public Guid Id { get; set; }

    [MaxLength(128)]
    [Column("description")]
    public string Description { get; set; }

    [ForeignKey("Student")]
    [Column("student_id")]
    public Guid StudentId { get; set; }

    [ForeignKey("Employer")]
    [Column("employer_id")]
    public Guid EmployerId { get; set; }

    [ForeignKey("Customer")]
    [Column("customer_id")]
    public Guid CustomerId { get; set; }

    public virtual Guid Student { get; set; }
    public virtual Guid Employer { get; set; }
    public virtual Guid Customer { get; set; }
}

And I see next snapshot of DbContext:

modelBuilder.Entity("DataInterface.Models.Order", b =>
{
    b.Property<Guid>("Id")
        .ValueGeneratedOnAdd()
        .HasColumnName("id");

    b.Property<string>("Description")
        .HasColumnName("description")
        .HasMaxLength(128);

    b.Property<Guid>("StudentId")
        .HasColumnName("student_id");

    b.Property<Guid>("EmployerId")
        .HasColumnName("employer_id");

    b.Property<Guid>("CustomerId")
        .HasColumnName("customer_id");

    b.HasKey("Id");

    b.HasIndex("StudentId");

    b.HasIndex("EmployerId");

    b.HasIndex("CustomerId");

    b.ToTable("order");
});

How can you see - all fine. But I need to create a composite key and delete primary key. So, let's do it. My new code of the entity (without Id primary key):

[Table("order")]
public class Order
{
    [MaxLength(128)]
    [Column("description")]
    public string Description { get; set; }

    [ForeignKey("Student")]
    [Column("student_id")]
    public Guid StudentId { get; set; }

    [ForeignKey("Employer")]
    [Column("employer_id")]
    public Guid EmployerId { get; set; }

    [ForeignKey("Customer")]
    [Column("customer_id")]
    public Guid CustomerId { get; set; }

    public virtual Guid Student { get; set; }
    public virtual Guid Employer { get; set; }
    public virtual Guid Customer { get; set; }
}

I add new behavior to the OnModelCreating method of my context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasKey(x => new { x.StudentId, x.EmployerId, x.CustomerId });
}

When I add new migration then I see that the index of StudentId was deleted. And I don't understand why? My new snapshot of DbContext:

modelBuilder.Entity("DataInterface.Models.Order", b =>
{
    b.Property<Guid>("Id")
        .ValueGeneratedOnAdd()
        .HasColumnName("id");

    b.Property<string>("Description")
        .HasColumnName("description")
        .HasMaxLength(128);

    b.Property<Guid>("StudentId")
        .HasColumnName("student_id");

    b.Property<Guid>("EmployerId")
        .HasColumnName("employer_id");

    b.Property<Guid>("CustomerId")
        .HasColumnName("customer_id");

    b.HasKey("StudentId", "EmployerId", "CustomerId");

    b.HasIndex("EmployerId");

    b.HasIndex("CustomerId");

    b.ToTable("order");
});

Why one of three indexes was deleted? Why b.HasIndex("StudentId"); line was deleted? What's wrong?

Note: I don't see this problem with a composite key from two fields in another tables.

1
1
4/17/2019 7:39:23 AM

Accepted Answer

The primary key is enforced by an index.

StudentId is the first column in that index.

It doesn't need another index defined on it (such an index would almost certainly be pointless too since it's likely that the PK index is the clustered index for the table and non-clustered indexes contain the PK columns at their leaves)

4
4/17/2019 7:43:28 AM

Popular Answer

An additional index on StudentId would be redundant. Since this is the first column in your primary key, a lookup on StudentId can simply use its index. Similarly a lookup on StudentId+EmployerId can do the same.

Anecdotally, this used to not be the case with EF Core, and two indexes were created. The fact that the index is being removed now means that this behavior has been fixed/optimized.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow