[FIXED] How does migrationBuilder determine which indexes to create?

Issue

This is using asp.net core 2.0, EF, visual studio 2017, sql server 2016, and creating a db migration via package manager console using 'add-migration' tool within the Package Manager Console.

I have a simple many-to-many relationship configured as below, 2 tables and a third 'joining table':

public class TblTrack
{
    public int ID { get; set; }

     ...

    //Navigation properties
    public List<TblProductItem> ProductItems { get; set; }
}


public class TblProduct
{
    public int ID { get; set; }

    ...

    //Navigation properties
    public List<TblProductItem> ProductItems { get; set; }
}

public class TblProductItem
{
    [Key]
    [Required]
    public int ProductID { get; set; }

    [Key]
    [Required]
    public int TrackID { get; set; }


    //Navigation properties
    public TblProduct Product { get; set; }
    public TblTrack Track { get; set; }
}

This is from the migration (generate in PMC) to create the joining table:

migrationBuilder.AddPrimaryKey(
name: "PK_tbl_ProductItems",
table: "tbl_ProductItems",
columns: new[] { "ProductID", "TrackID" });

migrationBuilder.CreateIndex(
name: "IX_tbl_ProductItems_TrackID",
table: "tbl_ProductItems",
column: "TrackID");

Please could someone explain:

What's the purpose of the index IX_tbl_ProductItems_TrackID?

Why was an index created for TrackID but not for ProductID?

Is there some other setting that determines which indexes will be created in the migration?

Solution

By default EF automatically creates Index (non-unique) on each property that is a foreign key reference.

Make sure that EF correctly created relation between TblProduct and TblProductItem(for example in SQL Server by expanding keys) - if not, specify relation explicitly using Fluent Api.

Regarding other setting you can require creating indexes using method in your Context class, but that index should be auto generated if foreign key relation is set.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TblProductItem>()
        .HasIndex(e => e.TrackID);
}

Answered By - tzu-ax

Answer Checked By - Mildred Charles (PHPFixing Admin)