Index Attribute in Entity Framework Core

Back to: ASP.NET Core Tutorials For Beginners and Professionals

Index Attribute in Entity Framework Core

In this article, I am going to discuss the Index Data Annotation Attribute in Entity Framework Core (EF Core) with Examples. Please read our previous article, where we discussed ForeignKey Attribute in Entity Framework Core 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 EF Core with Examples.

Index Attribute in Entity Framework Core

Entity Framework Core provides the [Index] attribute to create an index on a particular column in the database. 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 Core to create the corresponding index in the database.

Before creating an Index using EF Core, let us first understand some basic concepts of Index, like what an index is, 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 objects in a database used to improve search operations’ performance. 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. The following is the 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. The indexes can also be used if the select statement contains an “ORDER BY” clause.

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 in another. Moreover, the index will have pointers to the storage location of the actual data.

Note: When we create an Index using the Unique option, 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 guarantees that the column on which the index is created will not accept duplicate values.

How Does Index Affect the DML Operations?

The most important point you need to remember 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, deletes, or updates. As we know, whenever a product comes into the market, there should be some advantages and disadvantages to 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 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, it would be best if you used the indexes properly to balance the performance.

Example to Understand Index Attribute in Entity Framework Core:

You will see the following if you go to the Index Attribute class definition. As you can see, it has two constructors, a few properties, and a few overridden methods of the Object class.

Example to Understand Index Attribute in Entity Framework Core

Note: The most important point you need to remember is that you can apply the Index Attribute at the class level, and to the constructor, you need to specify the property or properties on which you want to create indexes.

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

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber))]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
    }
}

Next, modify the context class as follows:

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        public DbSet<Student>Students { get; set; }
    }
}

As we already discussed, whenever we add or update domain classes or configurations, we need to sync the database with the model using add-migration and update-database commands either using Package Manager Console or .NET Core CLI.

So, open Package Manager Console and Execute the following add-migration and update-database commands. You can give any name to your migration. Here, I am giving EFCoreDBMig1. The name that you are giving it should not be given earlier.

Index Data Annotation Attribute in Entity Framework Core (EF Core) with Examples

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

Index Data Annotation Attribute in Entity Framework Core (EF Core) with Examples

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 create 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 Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber), Name = "Index_RegistrationNumber")]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
    }
}

Now, open Package Manager Console and Execute the following add-migration and update-database commands.

Index Data Annotation Attribute in Entity Framework Core with Examples

Now, it should create the index with the specified name in the database, as shown in the below image.

Index Data Annotation Attribute in Entity Framework Core with Examples

Creating Index on Multiple Columns using EF Core:

It is also possible to create an Index on Multiple Columns. For this, we need to specify property names separated by a comma.

Let us understand this with an example. Please modify the Student class as follows. As you can see here, we have specified the RegistrationNumber and RollNumber properties in the Index Attribute. The Entity Framework Core will create one index based on the RegistrationNumber and RollNumber columns.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber), nameof(RollNumber), Name = "Index_RegistrationNumber_RollNumber")]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
        public int RollNumber { get; set; }
    }
}

Now, open Package Manager Console and Execute the following add-migration and update-database commands.

Creating Index on Multiple Columns using EF Core

If you verify the database, it should create the index with the specified name based on the two columns, as shown in the image below.

Creating Index on Multiple Columns using EF Core

How to Create Clustered and Unique Index using Entity Framework Core?

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

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

Note: It is impossible to manually create the Clustered Index using the Index Attribute in EF Core. A table can have a maximum of 1 clustered index, which will be created on the primary key column by default, and we cannot change this default behavior.

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

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber), Name = "Index_RegistrationNumber", IsUnique = true)]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
    }
}

With the above changes in place, now open Package Manager Console and Execute the following add-migration and update-database commands.

How to Create Clustered and Unique Index using Entity Framework Core?

Now, if you verify the database, then it should create a unique index with the specified name, as shown in the below image.

How to Create Clustered and Unique Index using Entity Framework Core?

Index Sort Order:

In most databases, each column covered by an index can be either ascending or descending. For indexes covering only one column, this typically does not matter: the database can traverse the index in reverse order as needed. However, for composite indexes, the ordering can be crucial for good performance and can mean the difference between an index getting used by a query or not. In general, the index columns’ sort orders should correspond to those specified in the ORDER BY clause of your query. The index sort order is ascending by default. You can make all columns have descending order as follows:

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber), nameof(RollNumber), AllDescending = true, Name = "Index_RegistrationNumber_RollNumber")]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
        public int RollNumber { get; set; }
    }
}

You may also specify the sort order on a column-by-column basis as follows.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(RegistrationNumber), nameof(RollNumber), IsDescending = new[] { false, true }, Name = "Index_RegistrationNumber_RollNumber")]
    public class Student
    {
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public int RegistrationNumber { get; set; }
        public int RollNumber { get; set; }
    }
}

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

Leave a Reply

Your email address will not be published. Required fields are marked *