Entity Framework - Index

An index is an on-disk data structure that is based on tables and views. Indexes make the retrieval of data faster and efficient, in most cases. However, overloading a table or view with indexes could unpleasantly affect the performance of other operations such as inserts or updates.

Indexing is the new feature in entity framework where you can improve the performance of your Code First apppcation by reducing the time required to query data from the database.

You can add indexes to your database using the Index attribute, and override the default Unique and Clustered settings to get the index best suited to your scenario.

Let’s take a look at the following code in which Index attribute is added in Course class for CourseID.

pubpc partial class Course {

   pubpc Course() {
      this.Enrollments = new HashSet<Enrollment>();
   }

   [Index]
   pubpc int CourseID { get; set; }
   pubpc string Title { get; set; }
	
   pubpc int Credits { get; set; }
   pubpc byte[] VersionNo { get; set; }
	
   pubpc virtual ICollection<Enrollment> Enrollments { get; set; }

}

The key created above is non-unique, non-clustered. There are overloads available to override these defaults −

To make an index a Clustered index, you need to specify IsClustered = true

Similarly, you can also make an index a unique index by specifying IsUnique = true

Let’s take a look at the following C# code where an index is clustered and unique.

pubpc partial class Course {

   pubpc Course() {
      this.Enrollments = new HashSet<Enrollment>();
   }

   [Index(IsClustered = true, IsUnique = true)]
   pubpc int CourseID { get; set; }
   pubpc string Title { get; set; }
	
   pubpc int Credits { get; set; }
   pubpc byte[] VersionNo { get; set; }
	
   pubpc virtual ICollection<Enrollment> Enrollments { get; set; }
}

Index attribute can be used to create a unique index in the database. However, this does not mean that EF will be able to reason about the uniqueness of the column when deapng with relationships, etc. This feature is usually referred to as support for “unique constraints”.