Don't forget to inform Entity Framework about your indexes. In your DbContext.OnModelCreating:

   .Property(person => person.Name)
        new IndexAnnotation(
            new IndexAttribute("IndexPersonNames", 0)));

This is fairly unreadable. Because I had to do this for a lot of properties, I decided to create an extension function:

static PrimitivePropertyConfiguration HasIndex(
    this PrimitivePropertyConfiguration property,
    string indexName,
    int columnOrder,
    bool uniqueIndexValues = false)
    var indexAttribute = new IndexAttribute(indexName, columnOrder)
        IsUnique = uniqueIndexValues,
    var indexAnnotation = new IndexAnnotation(indexAttribute);

    return property.hasColumnAnnotation(IndexAnnotation.AnnotationName, indexAnnotation);

The above modelBuilder statement will be:

   .Property(person => person.Name)
   .HasIndex("indexPersonNames", 0)));

or if you want to index on two columns:

.HasIndex("indexSurName", 0)         // first index by SurName
.HasIndex("indexFirstName", 1)       // then by FirstName

Related Articles