Index Attribute in Entity Framework Core

Index Attribute in Entity Framework Core

In this article, I will discuss the Index Data Annotation Attribute in Entity Framework Core (EF Core) with Examples. Please read our previous article discussing 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 (EF Core) provides the [Index] attribute to create an index on a particular column in the database. Creating an index on multiple columns using the Index Attribute is also possible. 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.

So, in Entity Framework Core, the Index attribute defines indexes on columns in your database to improve query performance. An index allows the database to efficiently look up rows based on the values in the indexed column(s), similar to how the index of a book helps you quickly locate information.

Example to Understand Index Attribute in Entity Framework Core:

If you go to the definition of Index Attribute, you will see the following. As you can see, it is a sealed class with two constructors and a few properties.

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, we need to specify the property or properties on which we want to create indexes.

Let us modify the Student Entity Class to use the Index Attribute. Here, you can see we have applied the Index Attribute on the 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 using Package Manager Console or .NET Core CLI.

So, open the Package Manager Console and Execute the add-migration and update-database commands as follows. 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 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, 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 the Package Manager Console and Execute the add-migration and update-database commands as follows.

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 composite 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 the Package Manager Console and Execute add-migration and update-database commands as follows.

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 Indexes Using Entity Framework Core?

By default, Entity Framework Core creates a Non-Clustered and Non-Unique Index. To create a Unique Index, you must 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, open the Package Manager Console and Execute add-migration and update-database commands as follows.

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, the ordering can be crucial for composite indexes for good performance. By default, the index sorting order is ascending. You can make all columns in 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; }
    }
}
Can we Create Multiple Indexes on a Table using EF Core?

Yes. It is possible to create multiple indexes on a table using EF Core. To achieve this, we must decorate the Entity with Multiple Index Attribute. For a better understanding, please modify the Student Entity as follows. Here, you can see we are creating two composite indexes. One index on the FirstName and LastName column with the name Index_FirstName_LastName. The other index is on the RegistrationNumber and RollNumber columns with the name Index_RegistrationNumber_RollNumber.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    [Index(nameof(FirstName), nameof(LastName), Name = "Index_FirstName_LastName")]
    [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; }
    }
}
Advantages and Disadvantages of using Indexes:
Advantages of using Indexes:
  • Performance: Properly indexed columns can dramatically speed up data retrieval times, making queries more efficient.
  • Uniqueness: By setting an index as unique, you ensure that no two rows have the same value in the indexed column(s).
Disadvantages of using Indexes:
  • Insert/Update Overhead: While indexes speed up data retrieval, they can slow down insert and update operations since the index needs to be updated whenever data changes.
  • Storage: Indexes consume additional disk space.
  • Over-indexing: It’s possible to have too many indexes. Over-indexing can lead to increased resource consumption and hurt performance more than it helps.

When deciding to add an index, consider the specific needs of your application. Typically, frequently searched or filtered columns are good candidates for indexing.

In the next article, I will discuss InverseProperty Attribute in Entity Core with Examples. I explain Index Data Annotation Attribute in Entity Framework Core with Examples in this article. 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 *