Gavin Draper's Blog

Father, Developer, Tech Geek, Extreme Sports Fanatic

@gavdraper | +gavdraper | github | StackOverflow Careers
 

Entity Framework Fluent API and Indexing

With the release of Entity Framework 6.1 the Fluent API can now be used to create indexes. It's still pretty basic and will hopefully evolve to become more complete in future releases.

For all the examples below I'm using the following model

public class People  
{
    public int Id { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string PhoneNumber { get; set; }
    public string NationalInsuranceNo { get; set; }
}

Samples

You can find the Visual Studio project for these samples at https://github.com/gavdraper/EntityFrameworkFluentApiIndexing

Single column Indexes

modelBuilder.Entity<People>()  
    .Property(x => x.Firstname)
    .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("ix_people_firstname")));

One thing to note here is that if your single column index matches the start of a multi column index the Entity Framework is smart enough not to create it as it's already covered by the multi column one.

Multi Column Indexes

modelBuilder.Entity<People>()  
    .Property(x => x.Firstname)
    .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("ix_people_fullname", 1)));
modelBuilder.Entity<People>()  
    .Property(x => x.Lastname)
    .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("ix_people_fullname", 2)));

Unique Indexes

modelBuilder.Entity<People>()  
    .Property(x => x.NationalInsuranceNo)
    .HasColumnAnnotation("Index",
        new IndexAnnotation(new IndexAttribute("ix_people_nationalinsurance") {IsUnique = true}));

Clustered Indexes

modelBuilder.Entity<People>()  
    .Property(x => x.Id)
    .HasColumnAnnotation("Index",
        new IndexAnnotation(new IndexAttribute("ix_people_nationalinsurance") { IsClustered = true}));

What's Not Possible Using Fluent API

As of Entity Framework 6.1.1

  • Include Fields
  • Sort Order (ASC,DESC)
  • Filtered Indexes

Whilst this is a big step forwards it still feels like a work in progress. I would hope to end up with a friendlier and more complete syntax for doing this something like...

modelBuilder.Entity<People>().Index("ix_people_firstname").SortByAsc();  

 
comments powered by Disqus