Index Attribute in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Index Attribute in Entity Framework with Examples

In this article, I am going to discuss Index Data Annotation Attribute in Entity Framework Code First Approach with Examples. Please read our previous article where we discussed ForeignKey Attribute in Entity Framework Code First Approach with Examples. At the end of this article, you will understand how to create Indexes (Clustered, Non-Clustered, and Unique or Non-Unique Indexes) using Entity Framework Code First Approach with Examples.

Index Attribute in Entity Framework

Entity Framework 6 provides the [Index] attribute to create an index on a particular column in the database. If you are using an earlier version of Entity Framework, then the Index Attribute will not work. It is also possible to create an index on one or more columns using the Index Attribute. Adding the Index Attribute to one or more properties of an Entity will cause Entity Framework to create the corresponding index in the database when it creates the database.

Before creating an Index using Entity Framework Code First Approach, let us first understand some basic concepts of an Index like what is Index, why we need an Index, what different types of Indexes and how the index impact DML Operations.

What is an Index and why Index in a Database?

Indexes in SQL Server are nothing but database object in a database which is used to improve the performance of search operations. When we create an index on any column or columns of a table, the SQL Server Database internally maintains a separate table called the Index Table. And when we are trying to retrieve the data from the original table, depending on the index table, SQL Server directly goes to the original table and retrieves the data very quickly.

Syntax for Creating an Index in SQL Server:

CREATE [UNIQUE] [CLUSTERED/ NON-CLUSTERED] INDEX <INDEX NAME> ON <TABLE NAME> (<COLUMN LIST>)

When SQL Server uses Indexes?

The SQL Server Database uses indexes of a table provided that the select or update or delete statement contained the “WHERE” clause and moreover the where clause condition column must be an indexed column. If the select statement contains an “ORDER BY” clause then also the indexes can be used.

Types of indexes in SQL Server

SQL Server Indexes are divided into two types. They are as follows:

1. Clustered Index: The Clustered Index in SQL Server defines the order in which the data is physically stored in a table. The Clustered Index by default was created when we created the primary key constraint for that table. That means the primary key column creates a clustered index by default. As a table can have only one primary key, a table can have only one clustered index. When a table has a clustered index then that table is called a clustered table. If a table has no clustered index its data rows are stored in an unordered structure.
2. Non-Clustered Index: In SQL Server Non-Clustered Index, the arrangement of data in the index table will be different from the arrangement of data in the actual table. The data is stored in one place and the index is stored in another place. Moreover, the index will have pointers to the storage location of the actual data.

Note: When we create an Index by using the Unique option then it is called Unique Index. Then the column(s) on which the unique index is created will not allow duplicate values. The Unique Index in SQL Server gives guarantees that the column on which the index is created will not accept any duplicate values.

How does Index Affect the DML Operations?

The most important point that you need to keep in mind is that indexes make the retrieval of data faster and more efficient, in most cases. However, creating a lot of Indexes in a table or view could affect the performance of other operations such as inserts, delete, or updates. As we know whenever a product comes into the market, there should be some advantages as well as disadvantages that exist in that product. This is also the same in the case of Indexes. So, Indexes Impact the DML Operations.

Suppose you modify any data using the Update or you delete some data using the Delete statement, then the updated information needs to be updated in all the indexes (i.e. in the index table) where the data has been changed. Indexes can help us to search and locate the data faster. However, too many indexes in a table can actually hurt the performance of DML operations. So, you need to use the indexes properly to balance the performance.

Example to Understand Index Attribute in Entity Framework:

If you go to the definition in the Index Attribute class, then you will see the following. As you can see, it is having three constructors, a few properties, and a few overridden methods of the Object class.

Note: The most important point that you need to remember is, you can apply the Index Attribute on Integral Property only using Entity Framework Code First Approach.

Let us modify the Student Entity Class as follows to use the Index Attribute. Here, you can see, we have applied the Index Attribute on the RegistrationNumber property. Here, we are using the constructor which does not take any parameter. In this case, an index will be created with the default naming convention and has no column order, clustering, or uniqueness specified.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCodeFirstDemo
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Index]
        public int RegistrationNumber { get; set; }
    }
}

As we are going to update the Entity Models many times, in order to avoid the Run-Time Exception when the model changes and when we rerun the application, let us set the database initializer as DropCreateDatabaseIfModelChanges. So, modify the context class as follows:

using System.Data.Entity;
namespace EFCodeFirstDemo
{
    public class EFCodeFirstContext : DbContext
    {
        public EFCodeFirstContext() : base("name=MyConnectionString")
        {
            //Setting the Database Initializer as DropCreateDatabaseIfModelChanges
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<EFCodeFirstContext>());
        }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }
        public DbSet<Student> Students { get; set; }
    }
} 

Please make sure to have the connection string with the name MyConnectionString within the app.config file or web.config file as shown in the below image.

Now, modify the Main method of the Program class as follows. Here, we are adding one student entity to the database.

using System;
namespace EFCodeFirstDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EFCodeFirstContext context = new EFCodeFirstContext())
            {
                var student = new Student() { FirstName = "Pranaya", LastName = "Rout", RegistrationNumber = 1001};
                context.Students.Add(student);
                context.SaveChanges();
                Console.WriteLine("Student Added");
                Console.ReadKey();
            }
        }
    }
}

With the above changes in place, now run the application. By default, it will create the Index with the name IX_{Property Name}. As we have Applied the Index Attribute on the RegistrationNumber property, EF API will create the Index with the name IX_ RegistrationNumber as shown in the below image. As you can see, by default, is created the Index as Non-Unique and Non-Clustered. Later I will show you how to create a unique and Clustered Index.

Now, you might have one question we applied the Index Attribute on a Single Property but here we can see two indexes. How is that possible? Yes, it is possible. This is because by default one Clustered Index is created when we created the Primary Key in a database.

Now, if you want to give a different name to your Index name rather than the auto-generated index name, then you need to use the other overloaded version of the Constructor which takes the name parameter. For a better understanding, modify the Student class as follows. Here, you can see we are providing the name as Index_RegistrationNumber.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCodeFirstDemo
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Index("Index_RegistrationNumber")]
        public int RegistrationNumber { get; set; }
    }
}

With the above changes in place, now run the application and it should create the index with the specified name as shown in the below image.

Creating Index on Multiple Columns using Entity Framework Code First Approach:

It is also possible to create an Index on Multiple Columns. For this, we need to use the overloaded version which takes the index name and order parameter. In this case, we need to specify the same name for both the Index Attribute. Let us understand this with an example. Please modify the Student class as follows. As you can see, here, we have applied the Index Attribute with the same name with RegistrationNumber and RollNumber properties and with the order values 2 and 1 respectively. In this case, the Entity Framework API will create one index based on the RegistrationNumber and RollNumber columns.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCodeFirstDemo
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Index("Index_Roll_Registration", 2)]
        public int RegistrationNumber { get; set; }

        [Index("Index_Roll_Registration", 1)]
        public int RollNumber { get; set; }
    }
}

With the above changes in place, now run the application and it should create the index with the specified name based on the two columns as shown in the below image.

The following SQL Script is generated for the composite Index in SQL Server.

CREATE NONCLUSTERED INDEX [Index_Roll_Registration] ON [dbo].[Students]
(
 [RollNumber] ASC,
 [RegistrationNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

How to Create Clustered and Unique Index using Entity Framework?

By default, Entity Framework API creates Non-Clustered and Non-Unique Index. If you want to create a Clustered and Unique Index, then you need to use the following two properties and you need to set the values to True.

1. IsClustered: Set this property to true to define a clustered index. Set this property to false to define a non-clustered index.
2. IsUnique: Set this property to true to define a unique index. Set this property to false to define a non-unique index.

Now, modify the Student class as shown below to create a clustered and unique index on the RegistrationNumber property.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCodeFirstDemo
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }

        [Index("Index_RegistrationNumber", IsClustered =true, IsUnique =true)]
        public int RegistrationNumber { get; set; }
    }
}

With the above changes in place, when you run the application code, you will get the following exception and this makes sense. As we know, we cannot create two clustered indexes in a table. Here, by default one clustered index is created based on the Primary which is created on the StudentId column and we are explicitly creating another clustered index on the RegistrationNumber column which is not possible, and throw the following exception.

Note: So, the point that you need to remember is using Data Annotation, we cannot create Clustered Index explicitly. By default, one and only one clustered index is created and that is too based on the key attribute.

In the next article, I am going to discuss InverseProperty Attribute in Entity Framework Code First Approach with Examples. Here, in this article, I try to explain Index Data Annotation Attribute in Entity Framework Code First Approach with Examples. I hope you enjoyed this Index Attribute in Entity Framework Code First Approach with Examples article. Please give your valuable feedback and suggestions about this article.