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.
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)
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.