Tuesday, 20 June 2017

Create Indexing in Entity Framework code first

You can create an index on one or more columns using the IndexAttribute. Adding the attribute to one or more properties will cause EF to create the corresponding index in the database when it creates the database, or scaffold the corresponding CreateIndex calls if you are using Code First Migrations.

Clustered Index:
In case if we are creating the clustered index on non-primary key column and want to make the uniqueness on the index column so we can use the “IsUnique” parameter of “IndexAttribute”.
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }

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

Using Fluent API
modelBuilder.Entity<Student>()
                .Property(x => x.RegistrationNumber)
                .HasColumnAnnotation("Index",
                    new IndexAnnotation(new IndexAttribute("RegistrationNumber") { IsClustered = true }));

By default, the index will be named IX_<property name> (i.e. IX_RegistrationNumber in the above example). You can also specify a name for the index though. The following example specifies that the index should be named RegistrationNumberIndex.

Clustered Index with Unique:
In case if we are creating the clustered index on non-primary key column and want to make the uniqueness on the index column so we can use the “IsUnique” named parameter of “IndexAttribute”. By default, indexes are non-unique.
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }

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

Using Fluent API
modelBuilder.Entity<Student>()
                .Property(x => x.RegistrationNumber)
                .HasColumnAnnotation("Index",
                    new IndexAnnotation(new IndexAttribute("RegistrationNumber") { IsClustered = true, IsUnique = true }));


Non-Clustered Index:
By default, index created by EF are non-clustered. So, modal class can have multiple non-clustered indexes. Non-clustered index also can be a unique in nature.
public class Student
{
    public int Id { get; set; }

    [Index] //default index creation without passing the name
    public string Name { get; set; }

     [Index("RegistrationNumber") //default index creation with passing the name
    public int RegistrationNumber { get; set; }
}

 


Single Column Index:

public class Student
{
    public int Id { get; set; }

    [Index ("Name")]
    public string Name { get; set; }  

    public int RegistrationNumber { get; set; }
}

 

Using Fluent API

modelBuilder.Entity<Student>()
                .Property(x => x.Name)
                .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("Name")));



Multi Column Index:

Indexes that span multiple columns can also be scaffold by using the same index name on multiple properties.
public class Student
{
            public int Id { get; set; }

            [Index("NameAndRegistration", Order: 1)]
            public string Name { get; set; }
           
            [Index("NameAndRegistration", Order: 2)]
            public int RegistrationNumber { get; set; }
}

Using Fluent API
modelBuilder.Entity<Student>()
                .Property(x => x.Name)
                .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("NameAndRegistration", 1)));
            modelBuilder.Entity<Student>()
                .Property(x => x.RegistrationNumber)
                .HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("NameAndRegistration", 2)));

 

 

Index Ordering:

Index can be order by setting the Ordernamed parameter of IndexAttribute.
public class Student
{
            public int Id { get; set; }

            [Index("Name", Order: 1)]
            public string Name { get; set; }
           
            [Index("RegistrationNumber", Order: 2)]
            public int RegistrationNumber { get; set; }
}




No comments:

Post a Comment