Index Attribute With Entity Framework 6.1

Introduction

In Entity Framework 4.3 and onward, it is possible to create and drop an index with Code First migration. However this had been done manually by editing the migration because we cannot include an index anywhere in a Code First model. With the Entity Framework 6.1, it is possible to add an index to a model and it can be created and dropped automatically by migrations.

An index is a data annotation attribute introduced with Entity Framework 6.1. The property of the model can be marked with an attribute and it should participate in a store index. The Index attribute allows us to create an index on one or more columns and we can specify the name of index.

The following are some important properties of the Index Attribute:

• IsClustered
  
  To create a cluster index, this property must be set to true. This property is set to false to define a non-cluster index. The default value of this property is false.
  
• IsUnique
  
  To create a unique index, this property must be set to true. This property is set to false to define a non-unique index. The default value of this property is false.
  
• Order
  
  This is a number that will be used to determine column ordering for the multi-column indexes. The value of this property is -1 if no column order has been specified.

Single column indexes Example

Suppose I have Department master entity and the definition of this entity is as below and accordingly I have create my DbContext class.

Model Class

public class DepartmentMaster
{
    [Key]
    public int DepartmentId { get; set; }
    public string Code { get; set; }
    [MaxLength(100)]
    [Index(“IX_Name_DepartmentMaster”, IsClustered = false)]
    public string Name { get; set; }
}

DbContext Class

public class Entities : DbContext
{
    public Entities()
        : base("name=Entities")
    {

    }
    public DbSet<DepartmentMaster> Departments { get; set; }
}

Use the following procedure to do the migration.

Step 1: Enable Migration

enable-migrations -ContextTypeName EntityFrameworkOtherFeature.Entities -MigrationsDirectory:EntitiesMigrations



Step 2: Add Migration Configuration

Add-Migration -configuration EntityFrameworkOtherFeature.EntitiesMigrations.Configuration Initial



Step 3: Update Database

Update-Database -configuration:EntityFrameworkOtherFeature.EntitiesMigrations.Configuration -Verbose



After the migration, the Index is being created with the name specified with the Index attribute. If we do not supply an index name then the migration creates an index with the default name (IX_columnName).



Multiple-column indexes

Indexes that span multiple columns are specified by using the same index name in multiple index annotation for the entity and also we can specify the order for the index column by using the order property of the Index attribute.

Example

Suppose I have an entity called OtherDepartment and I want to specify the index on the CompanyId and Name property. The Model definition and DbContext definition are as follows.

Model Class

public class OtherDepartment
{
    [Key]
    public int DepartmentId { get; set; }
    [Index("IX_Name_DepartmentMaster", IsClustered = false, Order = 1)]
    public int CompanyId { get; set; }
    public string Code { get; set; }
    [MaxLength(100)]
    [Index("IX_Name_DepartmentMaster", IsClustered = false, Order = 2)]
    public string Name { get; set; }
}

Now do the same procedure as described above for the migration (add the migration and update the database).





Foreign key Index conventions

The Index convention Code First convention causes indexes to be created for the columns used in the foreign key in the model even if these columns already have an Index attribute. That is because Migrations always have indexes created on foreign key columns. If we don't want to create this foreign key index, we can remove this using the following code.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<ForeignKeyIndexConvention>();
}

Summary

An Index Attribute creates an index in the database. Using this attribute, we can create an index on a single column as well as multiple columns.