score:1

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

modelBuilder.Entity<Person>()
   .Property(person => person.Name)
   .IsRequired()
   .HasColumnAnnotation(IndexAnnotation.AnnotationName, 
        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:

 modelBuilder.Entity<Person>()
   .Property(person => person.Name)
   .IsRequired()
   .HasIndex("indexPersonNames", 0)));

or if you want to index on two columns:

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

Related Articles