Entity Framework - Index

Advertisements

Previous Page
Next Page  

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 application 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.

public partial class Course {

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

   [Index]
   public int CourseID { get; set; }
   public string Title { get; set; }
	
   public int Credits { get; set; }
   public byte[] VersionNo { get; set; }
	
   public 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.

public partial class Course {

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

   [Index(IsClustered = true, IsUnique = true)]
   public int CourseID { get; set; }
   public string Title { get; set; }
	
   public int Credits { get; set; }
   public byte[] VersionNo { get; set; }
	
   public 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 dealing with relationships, etc. This feature is usually referred to as support for “unique constraints”.

Advertisements